Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
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,
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.
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.
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(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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |