Calculate YTD of average per Working date with dimension table

[LuITS]

I’m having trouble creating measures in Power BI to calculate an average per working day for specific quantities.

The basic (non‑YTD) calculation works fine, but I’m struggling to get the YTD version working correctly.

I have three tables:

• Date table (Date)

• Quantity table (Plant, Group, Quantity, Date, Key_Plant_Year_Month)

• Working days table (Plant, Year, Month, WorkingDays, Key_Plant_Year_Month)

Date[Date] 1 → n Quantity[Date]

WorkingDays[Key_Plant_Year_Month] 1 → n Quantity[Key_Plant_Year_Month]

Plant	Year	Month	WorkingDays
1	2024	1	22
3	2024	1	23
1	2024	2	24
3	2024	2	23

Non YTD Table:

Month	1		2
Year	2024		2024
Plant	Quantity	Avg per Working day	Quantity	Avg per Working day
1	66000 (22 WD)	3000	48000 (24 WD)	2000
3	46000 (23 WD)	2000	69000 (23 WD)	3000
Sum	112000	5000	117000	5000

*(!!! Sum Avg per Working day is the sum of the rows and the not result of sum of quantity / sum of workingDays)

I am able to get these correct values and sum with these two Measures:
I need to use sumx in the second meassure to not mess up with the working days, which cannot be summed up right?

Quantity per WD =

SUM('Quantity table'[Quantity]) / SUM('Working days table'[WorkingDays])

Avg per Wokring day  =

SUMX(Quantity,

[Quantity per WD])

My YTD Table should look like this, but i dont get the measures to work:

Month	1		2
Year	2024		2024
Plant	Quantity	Avg per Working day	Quantity	Avg per Working day
1	66000 (22 WD)	3000	114000 (46 WD)	2478,26
3	46000 (23 WD)	2000	115000 (46 WD)	2500
Sum	112000	5000	117000	4978,26

How should my YTD measures be designed so that Average per Working Day is calculated correctly?

The main challenge is to ensure that the calculation always uses the correct working days of each individual month and does not simply divide total quantity by total working days.

Maybe it is easier with an other Datamodell?

Thank you for your help.

[Ashish_Mathur]

Hi,

Please share the download link of the PBI file.  Show the problem there very clearly.

[Shai_Karmani · Accepted solution]

Your YTD numbers are the sum of per-plant YTD ratios, not the total YTD quantity divided by the total YTD working days. To get that pattern, iterate explicitly over plants and compute the ratio inside each iteration. Try:

Avg per Working day YTD =
SUMX (
    VALUES ( 'Quantity table'[Plant] ),
    CALCULATE (
        DIVIDE (
            SUM ( 'Quantity table'[Quantity] ),
            SUM ( 'Working days table'[WorkingDays] )
        ),
        DATESYTD ( 'Date'[Date] )
    )
)

For Plant 1 in Feb 2024 this returns (66000+48000)/(22+24) = 2478.26, and the cross-plant Sum becomes 2478.26 + 2500 = 4978.26, matching the table you posted. You cannot just wrap your existing SUMX(Quantity, [Quantity per WD]) in CALCULATE with DATESYTD, because that iterates daily rows and divides each by its own month's working days, so you end up with the sum of monthly averages instead of the YTD ratio. Make sure the Date table is marked as a date table, so the YTD filter flows through the Date and Key_Plant_Year_Month relationships correctly.

If this works for you, kindly mark it as the solution and give a thumbs up.

Best,
Shai Karmani

[LuITS]

@Shai_Karmani Thank you very much!

I found a solution by myself but yours is a lot cleaner. Thanks for the explanation.