Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am working on a report which shows YTD Values from the current year and I want to compare those to the previous year.
The only problem is, that the Data for the previous year is only available as a sum in December. (see graph)
I would like to show the previous year as an average of month 12 for all months.
Thank you for your help.
Solved! Go to Solution.
@Dominik_Eder , All data is there in dec. So try to change the numerator like
Force it get last year
(CALCULATE(SUM(fRegPL_data[Value]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,YEAR)),"12/31"))/12)
or use PREVIOUSYEAR like. That will take the complete year.
CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))
@amitchandak Thank you so much, while this wasn't the exact solution, it helped me figure it out.
I just had to get rid of the divide by 12 in the numerator, and add the ENDOFYEAR part to it and it worked
@Dominik_Eder , Allocate at day or month level and use
refer example : https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0
Or try something like this with a date table having month year column
Last YTD Sales = (CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))/12)*(CALCULATE(distinctcount('Date'[Month Year]),DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))/12)
Divide by 12 and multiply by No of Months
Hey @amitchandak , thank you for your help, but it doesn't seem to fix the problem.
I used your formula, just added a calculate around it, to filter for the needed data version (not relevant for my problem)
@Dominik_Eder , All data is there in dec. So try to change the numerator like
Force it get last year
(CALCULATE(SUM(fRegPL_data[Value]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,YEAR)),"12/31"))/12)
or use PREVIOUSYEAR like. That will take the complete year.
CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))
@amitchandak Thank you so much, while this wasn't the exact solution, it helped me figure it out.
I just had to get rid of the divide by 12 in the numerator, and add the ENDOFYEAR part to it and it worked
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |