Forum Discussion

tonny_bwg's avatar
tonny_bwg
Helper I
3 years ago
Solved

Help - Year Average Calculation

Dears：  I want to write a dax to calculate the average of 4 Quarters's value for previous Year，on the page , it has a slicer of Yearmonth, for example selecting 202307, I want to show 2022 year aver...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    tonny_bwg , if you do not have date, Create a date with help from month and year

    example

    date = date([Year], [Month no], 1)

     

    Join it with date of date table and then try a measure like

     

    Last YTD Sales = CALCULATE(Averagex(Values(Date[Qtr Year]), Calculate(SUM(Sales[Sales Amount]))) ,DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

    Last year Sales = CALCULATE(Averagex(Values(Date[Qtr Year]), Calculate(SUM(Sales[Sales Amount]))) ,DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     


    YTD Sales = CALCULATE(Averagex(Values(Date[Qtr Year]), Calculate(SUM(Sales[Sales Amount]))) ,previousyear('Date'[Date]))

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    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 :radacad sqlbi My Video Series Appreciate your Kudos.

amitchandak's avatar
amitchandak
Super User
3 years ago

tonny_bwg , if you do not have date, Create a date with help from month and year

example

date = date([Year], [Month no], 1)

 

Join it with date of date table and then try a measure like

 

Last YTD Sales = CALCULATE(Averagex(Values(Date[Qtr Year]), Calculate(SUM(Sales[Sales Amount]))) ,DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

Last year Sales = CALCULATE(Averagex(Values(Date[Qtr Year]), Calculate(SUM(Sales[Sales Amount]))) ,DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 


YTD Sales = CALCULATE(Averagex(Values(Date[Qtr Year]), Calculate(SUM(Sales[Sales Amount]))) ,previousyear('Date'[Date]))

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.