Forum Discussion

RMV's avatar
RMV
Helper 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.

     

     

RMV's avatar
RMV
Helper 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
Super 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.

 

 

  • RMV's avatar
    RMV
    Helper V
    9 years ago

    Hi GilbertQ,

    Thanks! The formula works.