cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BobKoenen
Helper IV
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 WipWorked hours Written offInvoicedEnd Wip
Project A100500200200200
Project B-501000050
Project C20001001000

 

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

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. 

View solution in original post

3 REPLIES 3
Stachu
Community Champion
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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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. 
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!