# Look at data for every year but for current year, only through May

• ### Question

• What is the best way to filter dates? Within the formula itself or with date filters? I tried the formula below...I want all data, and then for the most recent year, only data for the first 5 months, but it's just shwoing me the same info for all the years in the pivot.

Invoices per Claim Status Paid through May:=CALCULATE(([Count of Invoices Status Paid]/[Claims Count Paid Invoice Status]),DATESBETWEEN(DateTable[Date],Date(2013,1,1),Date(2016,5,30)))

Monday, September 12, 2016 5:18 PM

•
Hi AlexMartini,

I am trying to reproduce your scenario in my sample data and get the expected results as follows.
First,  your base calculation is [Sales] (EG: SUM(table[sales])), calculating the total sales over the last 10 months period selected is

SaleInperiod:=CALCULATE(SUM(Cost[sale]),DATESINPERIOD(Table2[date],MIN(Table2[date]),1*5,MONTH))

The same period(the first five month) calculation one in prior year is

SalesInPeriodLastYear:=CALCULATE([SaleInperiod],SAMEPERIODLASTYEAR(Cost[date]))

Compare periods fist five month from my last date in current year to prior year is

comparison:=[SaleInperiod]/[SalesInPeriodLastYear]

Then, you create a pivot table as follows, regard the year as rows, SaleInperiod , SalesInPeriodLastYear and comparison as Values.

In addition, you can use DATESBETWEEN function as below formula.

First five month:=CALCULATE(sum(Cost[sale]),DATESBETWEEN(Table2[date],MIN(Table2[date]), DATEADD(Table2[date],5,MONTH)))

For reference:

https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/

http://www.powerpivotpro.com/2010/07/use-time-intelligence-functions-to-do-a-running-sum-of-the-last-6-months-with-powerpivot/.

If this is not what you what, please give us more details and sample data structure.

Regards,
Angelia

Tuesday, September 20, 2016 10:05 AM

### All replies

• That's because your measure removes filters from Date and returns value for all dates inside DATESBETWEEN.

This is enough but...

Invoices per Claim Status Paid through May:=[Count of Invoices Status Paid]/[Claims Count Paid Invoice Status]

... to properly filter it in your pivot you should use a  column like this:

 Year_month ... 2013/01 2013/02 2013/03 2013/04 2013/05 2013/06 2013/07 2013/08 2013/09 2013/10 2013/11 2013/12 2014/01 2014/02 2014/03 2014/04 2014/05 2014/06 2014/07 2014/08 2014/09 ...

Tuesday, September 13, 2016 1:15 PM
•
Hi AlexMartini,

I am trying to reproduce your scenario in my sample data and get the expected results as follows.
First,  your base calculation is [Sales] (EG: SUM(table[sales])), calculating the total sales over the last 10 months period selected is

SaleInperiod:=CALCULATE(SUM(Cost[sale]),DATESINPERIOD(Table2[date],MIN(Table2[date]),1*5,MONTH))

The same period(the first five month) calculation one in prior year is

SalesInPeriodLastYear:=CALCULATE([SaleInperiod],SAMEPERIODLASTYEAR(Cost[date]))

Compare periods fist five month from my last date in current year to prior year is

comparison:=[SaleInperiod]/[SalesInPeriodLastYear]

Then, you create a pivot table as follows, regard the year as rows, SaleInperiod , SalesInPeriodLastYear and comparison as Values.

In addition, you can use DATESBETWEEN function as below formula.

First five month:=CALCULATE(sum(Cost[sale]),DATESBETWEEN(Table2[date],MIN(Table2[date]), DATEADD(Table2[date],5,MONTH)))

For reference:

https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/

http://www.powerpivotpro.com/2010/07/use-time-intelligence-functions-to-do-a-running-sum-of-the-last-6-months-with-powerpivot/.

If this is not what you what, please give us more details and sample data structure.

Regards,
Angelia

Tuesday, September 20, 2016 10:05 AM