Calculating MTD / YTD averages by working days

[RaMiRo87]

Hi all,

I have a little issue with some average measures and I need urgently help with it.

I need to create MTD and YTD measures which provide me with the average for sales and order sums. The average by then must be filtered by the number of business days. I have some columns in my date table which could be helpful e.g.:

The measures will be displayed on a dashboard with a standard range date slicer. This means, that the users can select any start and end date they like. 

By now I tried several simple approaches, e.g.:

CALCULATE(

    AVERAGEX(fSalesControlling, [MTD Incoming Orders (AY)]),

    dTime[Business Day (Y/N) EN] = "Yes")

Would be glad if you could help me out!

Thanks for your responses in advance!

Cheers, 

Ramon

[Anonymous · Accepted solution]

Hi @RaMiRo87 ,

You can try formula like below:

M_ =
VAR total_ =
    TOTALYTD (
        SUM ( 'Table'[Value] ),
        'Table'[Date],
        FILTER ( 'Table', 'Table'[businessDay] = "Yes" ),
        "31/12"
    )
VAR count_ =
    COUNTROWS (
        FILTER (
            'Table',
            YEAR ( 'Table'[Date] ) = YEAR ( TODAY () )
                && 'Table'[businessDay] = "Yes"
        )
    )
RETURN
    DIVIDE ( total_, count_ )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RaMiRo87]

Thank you very much!

Looks a lot like my own approach, but yours is working! 😄

Thanks a lot and all the best!

[johnbasha33]

@RaMiRo87 

To create MTD (Month-to-Date) and YTD (Year-to-Date) measures for average sales and order sums filtered by the number of business days, you can use the following approach:

1. **MTD Average Measure**:
This measure calculates the average sales or order sums for the month-to-date period, considering only business days.

```DAX
MTD Average =
VAR SelectedStartDate = MIN(dTime[Date])
VAR SelectedEndDate = MAX(dTime[Date])
VAR BusinessDaysInMTD =
CALCULATE(
COUNTROWS(dTime),
dTime[Date] >= DATE(YEAR(SelectedStartDate), MONTH(SelectedStartDate), 1),
dTime[Date] <= SelectedEndDate,
dTime[Business Day (Y/N) EN] = "Yes"
)
RETURN
IF(
ISBLANK(BusinessDaysInMTD),
BLANK(),
AVERAGEX(
FILTER(
fSalesControlling,
fSalesControlling[Date] >= DATE(YEAR(SelectedStartDate), MONTH(SelectedStartDate), 1) &&
fSalesControlling[Date] <= SelectedEndDate
),
fSalesControlling[YourSalesOrOrderMeasure]
) / BusinessDaysInMTD
)
```

2. **YTD Average Measure**:
This measure calculates the average sales or order sums for the year-to-date period, considering only business days.

```DAX
YTD Average =
VAR SelectedStartDate = MIN(dTime[Date])
VAR SelectedEndDate = MAX(dTime[Date])
VAR BusinessDaysInYTD =
CALCULATE(
COUNTROWS(dTime),
YEAR(dTime[Date]) = YEAR(SelectedStartDate),
dTime[Date] <= SelectedEndDate,
dTime[Business Day (Y/N) EN] = "Yes"
)
RETURN
IF(
ISBLANK(BusinessDaysInYTD),
BLANK(),
AVERAGEX(
FILTER(
fSalesControlling,
YEAR(fSalesControlling[Date]) = YEAR(SelectedStartDate) &&
fSalesControlling[Date] <= SelectedEndDate
),
fSalesControlling[YourSalesOrOrderMeasure]
) / BusinessDaysInYTD
)
```

Replace `fSalesControlling` with your sales or order fact table, and `YourSalesOrOrderMeasure` with the appropriate measure column.

These measures calculate the average sales or order sums for the selected MTD or YTD period, considering only the business days within that period. Ensure that your date table (`dTime`) includes the appropriate business day flag column for filtering.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!