March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
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
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?
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
glad to hear you solved it yourself 🙂
can you mark your post as solved, so it can help others in the future?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
16 | |
15 | |
12 | |
11 |
User | Count |
---|---|
32 | |
26 | |
24 | |
20 | |
14 |