Create Group average measure

DAX DAX Calculations
1

I’m sure this has been solved somewhere before but I’m not finding it.
I’ve created an average billable per day number for a business that is only open M-F which aligns with the data table workday definition.

The average works great when sorted by month but I also want to include a YTD Average per day to compare against as time goes along.
Calculate seems like the correct DAX term but for the life of me I can’t figure out the date filter to exclude all months in the datetable that have yet to occur (or have data).
This business dataset only has Sept & Oct 2021 data currently collected and I’ve tried to filter to only sept/oct 2021 but obviously a hard filter doesn’t allow for the data to update as dates pass.

GroupAvereage.pbix (184.7 KB)

image
image1386×662 23.9 KB

2

Hi @gkavesh ,

Thanks for posting this question and sharing your sample file. I got 2 separate solutions as I am not sure which one will suit you better as there were only 4 working days in September in your dataset, so average can be different. Below are both measures:

image
image770×419 13.6 KB 

WorkDaysInPeriod = 
VAR mindate =
    MIN ( 'DetailCPT'[Date Of Service] )
RETURN
    CALCULATE (
        COUNT ( DateTable[Date] ),
        DateTable[Day Type] = "Weekday",
        FILTER ( 'DateTable', [All Billed] <> BLANK () && 'DateTable'[Date] >= mindate )
    )

This is the 2nd measure and output and since it is calculating average for whole September, so its average is less as in September you have only 4 days of data.

image
image750×398 11 KB 

    WorkDaysInPeriod = 
   CALCULATE (
        COUNT ( DateTable[Date] ),
        DateTable[Day Type] = "Weekday",
        FILTER ( VALUES ( 'DateTable'[Month & Year] ), [All Billed] <> BLANK () )
    )

I hope this solution helps you. Please feel free to get in touch in case of any problem in the solution.

Kind Regards,
Hafiz

3

Thank you and good point about the 4 days in September which I had forgotten.
The first solution actually works great to address that detail and other potential users of this dashboard who’s first months on the platform are less than full months.

Also points out that I had overly simplified the logic on the working day element.

Again much thanks

1 Like