cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

Get the cumulatieve value before the selected month WIP

Hi all,

I am trying to create a Work in Proces overview which shows the total amount to be invoiced. This overview has a from till slicer on month level. The first column always needs to show the cumulative values of all months before the first month selected in the slicer.

See a simpel model for example from month 2 til month 4:

 Selected months Start Wip Worked hours Written off Invoiced End Wip Project A 100 500 200 200 200 Project B -50 100 0 0 50 Project C 200 0 100 100 0

If I would change the Slicer to from month 5 til month x The value of the [Start WIP] would be the [end Wip] in the table above.

The end Wip = (begin WIP+worked hours) - (written off + Invoiced)
I am struggeling to get the formula for the [Start Wip], This is essentially the cumulative [end Wip] of the month before the selected month.

However I think I am getting into a Loop and I cannot figure out how to het the total from the selected month -1.

Hope you can help me

1 ACCEPTED SOLUTION
Helper IV

Hi stachu,

Thankx for you reply. It took a while for anybody to respond so i have figured it out myself. I used this formula

OHW Begin periode =
var mindate = CALCULATE(FIRSTDATE('Calendar'[Date]);ALL('Calendar'[Date]))

return
CALCULATE( [OHW eind -1];
FILTER(ALL('Calendar');'Calendar'[Date] < mindate))

this always gets me the cumulative totals of the months before the selected months. Regardles if I select multiple months.
3 REPLIES 3
Community Champion

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

 Column1 Column2 A 1 B 2.5

I mean the input tables, as I assume the one you posted is your expected output, correct?

Thank you for the kudos 🙂

Helper IV

Hi stachu,

Thankx for you reply. It took a while for anybody to respond so i have figured it out myself. I used this formula

OHW Begin periode =
var mindate = CALCULATE(FIRSTDATE('Calendar'[Date]);ALL('Calendar'[Date]))

return
CALCULATE( [OHW eind -1];
FILTER(ALL('Calendar');'Calendar'[Date] < mindate))

this always gets me the cumulative totals of the months before the selected months. Regardles if I select multiple months.
Community Champion

glad to hear you solved it yourself 🙂
can you mark your post as solved, so it can help others in the future?

Thank you for the kudos 🙂

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors