Forum Discussion
Helper Vprevious 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.
What I have is transaction table, the example is follow
| Date | Amount |
| 1-Jan-16 | 1000 |
| 5-Jan-16 | 500 |
| 2-Feb-16 | 400 |
| 11-Feb-16 | 1700 |
| 2-Jan-17 | 700 |
| 10-Jan-17 | 500 |
| 20-Jan-17 | 1000 |
| 3-Feb-16 | 2000 |
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(Table[Amount]),FILTER(Table,AND(Table[Date]>=DATE(YEAR(TODAY())-1,1,1),Table[Date]<=DATE(YEAR(TODAY())-1,MONTH(MAX(Table[Date])),DAY(ENDOFMONTH(Table[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.
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.
7 Replies
RMVHi,
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
Date Amount 1-Jan-16 1000 5-Jan-16 500 2-Feb-16 400 11-Feb-16 1700 2-Jan-17 700 10-Jan-17 500 20-Jan-17 1000 3-Feb-16 2000 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.
- GilbertQ
Super User
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
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
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,