Measure with YTD running average shown per month

[HLVW]

Hi.

I would like to create a measure caluculating the green column below. For each month I have the actual sales and the number of working days. The outcome should be a line chart where you see the accumulated sales per working day. I keep getting the monthly average in the blue column, so any suggestions to get to the green result? 🙂

Thanks a lot in advance.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-yilong-msft · Accepted solution]

Hi @HLVW ,

I create a table as you mentioned.

Then I create a measure named Avg. Sales pr day.

Avg. Sales pr day =
CALCULATE (
    SUM ( 'Table'[Monthly Sales] ) / SUM ( 'Table'[Working days] ),
    FILTER (
        ALLSELECTED ( 'Table'[Month] ),
        ISONORAFTER ( 'Table'[Month], MAX ( 'Table'[Month] ), DESC )
    )
)

Finally I create another measure to get what you want.

Avg. Sales pr day YTD =
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Acc. Sales] ), ALLSELECTED ( 'Table'[Month] ) ),
    CALCULATE ( SUM ( 'Table'[Acc. Days] ), ALLSELECTED ( 'Table'[Month] ) )
)

Best Regards

Yilong Zhou

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

[rajendraongole1]

Hi @HLVW - I mean instead of colors, as i understood, the logic to calculate the average sales per day 

can you try the below dax logic with datesytd function and number of days ytd as seperate measures

-- Total Sales YTD
TotalSalesYTD =
CALCULATE(
SUM(Sales[SalesAmount]),
DATESYTD('Date'[Date])
)

-- Number of Days YTD
DaysYTD =
CALCULATE(
COUNTROWS('Date'),
DATESYTD('Date'[Date])
)

-- Average Sales Per Day YTD %
AverageSalesPerDayYTD =
DIVIDE([TotalSalesYTD], [DaysYTD])

if the above not works, please try the solution link below for your reference.

Solved: ytd running - Microsoft Fabric Community

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