Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dominik_Eder
New Member

Show Average over year with only month 12 values available

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)

Untitled.png

 

I would like to show the previous year as an average of month 12 for all months.

 

Thank you for your help. 

2 ACCEPTED SOLUTIONS

@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]))

View solution in original post

@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

 

Last YTD Sales = (CALCULATE((CALCULATE(SUM(fRegPL_data[Value]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,YEAR)),"12/31"))),fRegPL_data[Data_version_key]="15")/1000)
*(CALCULATE(distinctcount('Date'[MonthYear]),DATESYTD(dateadd('Date'[Date],-1,YEAR),"12/31"))/12)
 
This was the final solution, for anyone wondering. blue parts are just for my case.

 

 

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

So, just divide by 12?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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)

 

Last YTD Sales = CALCULATE((CALCULATE(SUM(fRegPL_data[Value]),DATESYTD(dateadd('Date'[Date],-1,YEAR),"12/31"))/12)*(CALCULATE(distinctcount('Date'[MonthYear]),DATESYTD(dateadd('Date'[Date],-1,YEAR),"12/31"))/12),fRegPL_data[Data_version_key]="15")/1000
 
This was the result:
Screenshot2.png
This would probably be the right amount for the month dec, but the other months aren't showing, and a YTD view would be much better then an average. I understand the logic of the formula, just not why it doesn't seem to wrk for me. Thank you.
 
This is the date Table, it is seperate, but linked (form Jan2018-Dec2021)
 
screenshot3.png
 

 

 

@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

 

Last YTD Sales = (CALCULATE((CALCULATE(SUM(fRegPL_data[Value]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,YEAR)),"12/31"))),fRegPL_data[Data_version_key]="15")/1000)
*(CALCULATE(distinctcount('Date'[MonthYear]),DATESYTD(dateadd('Date'[Date],-1,YEAR),"12/31"))/12)
 
This was the final solution, for anyone wondering. blue parts are just for my case.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.