How to calculate a measure on different time periods with Total YTD and Average YTD

[Anonymous]

Hi, 

Would you have some advices on the best approach to follow to display data with a measure calculated on different time periods like this : 

Order type	2017	2018	2019	YTD Total aaaa	YTD Average aaaa	Current month
Type A	Total of order values for 2017 for type A	Total of order values for 2018 for type A	Total of order values for 2019 for type A	YTD Total of order values for current year (or selected year)	YTD Average of order values for current year (or selected year)	Total of order values for current month (or selected month)
Type B	...
Type C
Total

As of now, I have created these measures : 

SumofMontantPos = sum(Factcommandes[montant_position])

NbCde = DISTINCTCOUNT(Factcommandes[Code_commande])

AvgofMontantCde = DIVIDE([SumofMontantPos],[NbCde])

YTDSumOfMontantCde = TOTALYTD([SumofMontantPos],DimDate[PKDate])

YTDAvgOfMontantCde = TOTALYTD([AvgofMontantCde],DimDate[PKDate])

I have a date dimension (DimDate).

So, as of now, when I select one month in my date filter, I am able to display the values for : current month, YTD Total and YTD Average.

My question is how to add the value for the 3 previous years ? is there a "clean" way to do so ? Thanks a lot for your advices.

Cheers, AnneSo

[amitchandak · Accepted solution]

@Anonymous , You can not have hybrid display.

You can create a column for year with time intelligence and date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
2nd Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-2))

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Hybrid Table Idea  -  https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39773011-hybrid-table