Dax measure to calculate Sales by working day with relation to MTD sales

[Anonymous]

I want to be able to prepare a report to calculate the forecasted/Targeted sales by workday in a month and compare it to actual. Let say we have 23 workdays in a month, and forecasted sales is $20,000, and today is workday 4, the forecasted sales MTD would be 20,000/23x4. Please how do I go about this?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file. In the sample, I define Saturday and Sunday as non working day.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Forecast MTD: =
VAR _monthlyforecast =
    CALCULATE (
        SUM ( Forecast[Forecast Sales] ),
        ALL ( 'Calendar'[Date] ),
        VALUES ( 'Calendar'[Year-Month sort] )
    )
VAR _monthworkdayscount =
    COUNTROWS (
        FILTER (
            ALL ( 'Calendar' ),
            NOT ( 'Calendar'[Day number] IN { 1, 7 } )
                && 'Calendar'[Year-Month sort] = MAX ( 'Calendar'[Year-Month sort] )
        )
    )
VAR _uptotoday =
    COUNTROWS (
        FILTER (
            ALL ( 'Calendar' ),
            NOT ( 'Calendar'[Day number] IN { 1, 7 } )
                && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
                && 'Calendar'[Year-Month sort] = MAX ( 'Calendar'[Year-Month sort] )
        )
    )
RETURN
    DIVIDE ( _monthlyforecast, _monthworkdayscount ) * _uptotoday