Forum Discussion

RMV's avatar
RMV
Icon for Helper V rankHelper V
9 years ago
Solved

previous year calculation with time filter

Hi,   I have this difficulty in applying a correct formula. I'm trying to apply DAX formula to get the previous year calculation, while I also have Month Slicer applied for my financial report. W...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    Hi RMV

     

    When you created your date table did you create the relationships?

     

    I created the following measures based on your dataset.

     

    My Amount = SUM(Table1[Amount])

YTD = TOTALYTD([My Amount],'Date'[Calendar Date])

Previous Yr My Amount = CALCULATE([My Amount],PREVIOUSYEAR('Date'[Calendar Date]))

    Which then gave me the following correct output.

     

     

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago

Hi RMV


What I would suggest doing is to create a date table, after which you could then use the PREVIOUSYEAR and SAMEPERIODLASTYEAR.

You can find details below on how to create the date table, and this is required and recommended for when doing any TIME related DAX functions

 

https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fiscal-attributes-using-power-query/

 

You are indeed correct that if you put in a slicer and click on it, it will then filter your data based on the slicer selected for all your measures on your report. But depending on how you create your measure you might be able to change the filter context for previous periods.

RMV's avatar
RMV
Icon for Helper V rankHelper V
9 years ago

Hi GilbertQ,

 

thanks for your advise.

I need further advise on using the date table.

I apologize that my example did not really represent the condition.

Beside the Date & Amount column, there're some category columns that will be used to drill down the data from category to category.

 

The table result I'm looking for is as follow

Category 1                   Amount YTD    Amount YTD Previous Year

A

    A-1                            xxx                      xxx

    A-2                            xxx                      xxx

B

    B-1                            xxx                      xxx

Note: A & B are category 1

A-1, A-2, B-1 are category 2

 

If I use the date table, how can I still drill down to categories?

 

regards,

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi RMV

     

    Yes you will still be able to use the drill down categories

    • RMV's avatar
      RMV
      Icon for Helper V rankHelper V
      9 years ago

      Hi GilbertQ,

       

      I tried it, and still got blank as the result.

      Need further advise on what goes wrong.

       

      I created a date table, add a measure column = CALCULATE(SUM(Table[Amount]),PREVIOUSYEAR(DateCalendar[Date]))

       

      In the table matrix visualization:

      Category                            Amount YTD                   Amount Previous Year

      (column from Table)           (measure from Table)    (measure from DateCalendar)

       

      Tried CALCULATE(SUM(Table[Amount]),SAMEPERIODLASTYEAR(DateCalendar[Date]))

      It results blank too.

      • GilbertQ's avatar
        GilbertQ
        Icon for Super User rankSuper User
        9 years ago

        Hi RMV

         

        When you created your date table did you create the relationships?

         

        I created the following measures based on your dataset.

         

        My Amount = SUM(Table1[Amount])

YTD = TOTALYTD([My Amount],'Date'[Calendar Date])

Previous Yr My Amount = CALCULATE([My Amount],PREVIOUSYEAR('Date'[Calendar Date]))

        Which then gave me the following correct output.