Forum Discussion

NrAg's avatar
NrAg
Frequent Visitor
3 years ago

Yearly Average based on Quarterly Numbers

Hi there! 

 

I am wondering about the following and wanted to ask if you have alternativ solutions:

Lately, I had to calculate an annual indicator which was based on the average of the individual value of each quarter. 

This is a very simplified version of the code to explain: 

 

Measure Yearly = 
VAR SelectedYear = SELECTEDVALUE ( Calendar[Year] )
VAR EndDateQ1 = DATE ( SelectedYear, 3, 31 )
VAR EndDateQ2 = DATE ( SelectedYear, 6, 30 )
VAR EndDateQ3 = DATE ( SelectedYear, 9, 30 )
VAR EndDateQ4 = DATE ( SelectedYear, 12, 31 )
VAR Result = 
CALCULATE ( COUNTROWS ( FILTER ( SampleTable, SampleTable[OrderDate] <= EndDateQ1 && SampleTable[DeliveryDate] > EndDateQ1 ) ) ) 
+ CALCULATE ( COUNTROWS ( FILTER ( SampleTable, SampleTable[OrderDate] <= EndDateQ2 && SampleTable[DeliveryDate] > EndDateQ2 ) ) ) 
+ CALCULATE ( COUNTROWS ( FILTER ( SampleTable, SampleTable[OrderDate] <= EndDateQ3 && SampleTable[DeliveryDate] > EndDateQ3 ) ) ) 
+ CALCULATE ( COUNTROWS ( FILTER ( SampleTable, SampleTable[OrderDate] <= EndDateQ4 && SampleTable[DeliveryDate] > EndDateQ4 ) ) ) 
RETURN 
Result / 4

 

The measure works fine. However, it was quite inconvenient to establish and customize and I do feel that there are better ways to extract the respective number. Does anyone have an idea or suggestion how to improve the code?

 

Looking very much forward to hearing from you and discussing solutions. 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    NrAg , better to have qtr year in date table and have measure like

     

    YTD Sales = CALCULATE(AvergaeX(Values('Date'[Qtr Year]), CALCULATE(SUM(Sales[Sales Amount]))),DATESYTD('Date'[Date],"12/31"))

     

    or

     

    Sales = CALCULATE(AvergaeX(Values('Date'[Qtr Year]), CALCULATE(SUM(Sales[Sales Amount]))) )

     

    DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
    https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

     

     

     

    Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
    , "Year", year([date])
    , "Month Year", format([date],"mmm-yyyy")
    , "Month year sort", year([date])*100 + month([date])
    , "Qtr Year", format([date],"yyyy-\QQ")
    , "Qtr", quarter([date])
    , "Month",FORMAT([Date],"mmmm")
    , "Month sort", month([DAte])
    , "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
    ,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
    , "Month Type", Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
    eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
    Format([Date],"MMM-YYYY") )
    ,"Year Type" , Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )
    )

    • NrAg's avatar
      NrAg
      Frequent Visitor
      3 years ago

      Thank you a lot Amit for your response! I am just not sure if that is solving the issue. The problem is that in this example I only want to count the number of entries that have been ordered within the respective quarter but delivered after that quarter. And afterwards get an average of that. 

      And also big thanks for your calendar table! Very helpful! I would add a DateKey column for building performant relationships with integer values. 🙂  

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        3 years ago

        NrAg,

         

        Try this measure. The relationship between DimDate and SampleTable is on Order Date.

         

        Measure Yearly = 
AVERAGEX (
    VALUES ( DimDate[Quarter End Date] ),
    VAR vQuarterEndDate = DimDate[Quarter End Date]
    RETURN
        CALCULATE (
            COUNTROWS ( SampleTable ),
            SampleTable[Delivery Date] > vQuarterEndDate
        )
)