Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |