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 average in card. I got blank in the card with below codes. does anyone help me to solve this issue?
thanks 
--Tonny

 
Codes is as below:

test tbl =
//Inventory % of sales_Year  
 VAR _curyear = [CurrentYear]
 VAR _filteredtbl = FILTER(ADDCOLUMNS(
                        DISTINCT(
                          SELECTCOLUMNS(dim_Date,
                                 "Year",[Year],
                                 "YearQuarter", [YearQuarter])),
                                 "Data", [Inventory % of sales_Quarter]),
                        [Year]=_curyear - 1)
 
Return
  // AVERAGEX(_filteredtbl,[Data])
  CALCULATE(COUNTROWS(_filteredtbl),_filteredtbl)

 

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.

1 Reply

  • 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.