locked
Look at data for every year but for current year, only through May RRS feed

  • 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

Answers

  •          
    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