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.

     

     

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

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.

What I have is transaction table, the example is follow

 

DateAmount
1-Jan-161000
5-Jan-16500
2-Feb-16400
11-Feb-161700
2-Jan-17700
10-Jan-17500
20-Jan-171000
3-Feb-162000

 

In the page where I add the visualization, I have a Month slicer. and the formula I tried to apply is:

Amount YTD Previous Year = CALCULATE(SUM(GLEntry_all[Amount_]),FILTER(DateCalendar,AND(DateCalendar[Date]>=DATE(YEAR(TODAY())-1,1,1),DateCalendar[Date]<=DATE(YEAR(TODAY())-1,MONTH(MAX(DateCalendar[Date])),DAY(ENDOFMONTH(DateCalendar[Date]))))))

But this formula returns blank amount. I'm guessing this is because of the Month slicer applied is for 2017.

For the information, I don't have any other filter in the visual nor page nor report.

 

I tried to use SAMEPERIODLASTYEAR or PREVIOUSYEAR formula, but I guess these work only for unique calendar date (?)

 

I need help, what goes wrong with my formula, and what solution I need to apply.

Thanks.