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 ...
Need Help
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
        )
)