Forum Discussion
Need help with DAX formula
Hi,
I have below table and need to calculate below:
1. Calculate Cumulative Sum of Forecast Value till today's date i.e. 15th Aug
2. Add these i.e. 'Value to Add' + Cumulative Sum = 15 + 75 = 90
3. Find the date in date column where Cumulative Sum is equal or just less than above value i.e. 90. The result should be 16th-Aug-23
Any help is greatly appreciated, many thanks!
sumitnegi1266 , Hop you already have measure
Cumm Sum = CALCULATE(SUM(Table[Value]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Date = Minx(Values('Date'[date]), if([Cumm Sum]>90, [Date], blank())) -1
or
Date = calculate( Minx(Values('Date'[date]), if([Cumm Sum]>90, [Date], blank())) , allselected()) -1
3 Replies
- amitchandak
Super User
sumitnegi1266 , Hop you already have measure
Cumm Sum = CALCULATE(SUM(Table[Value]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Date = Minx(Values('Date'[date]), if([Cumm Sum]>90, [Date], blank())) -1
or
Date = calculate( Minx(Values('Date'[date]), if([Cumm Sum]>90, [Date], blank())) , allselected()) -1
It worked as expected. Thank you so much amitchandak 🙂
amitchandak I need to now calculate the cumulative sum at a certain date. I have a days measure which I am adding with the date output e.g. 16th Aug + 4 days = 20th Aug. I then need to calculate Cumulative sum for 20th Aug, which should be 160
