Forum Discussion
split value equally into months and cumulative
- 2 years ago
Thank you Ahmedx
You solution is very close but for example PO 60800001452 should not have value in 2024 Jan and Feb.
I have managed to solve this problem by
- Create a column to count the number of months between start and end date
Month Diff = DATEDIFF('BOH Hours'[Start],'BOH Hours'[End],month)+1- Create a column to count the average hours per month
AVG hours per month = DIVIDE('BOH Hours'[Contract Qty],'BOH Hours'[Month Diff])- Use the following Syntax (I found it in another post) to find the cumulative SUM in the range
Cumulative Contract Qty (within range) = VAR _s = SELECTEDVALUE( 'BOH Hours'[Start] ) VAR _e = SELECTEDVALUE( 'BOH Hours'[End] ) VAR _p = SELECTEDVALUE( 'BOH Hours'[PO] ) VAR _inrangeHours = CALCULATE( SUM( 'BOH Hours'[AVG hours per month] ), FILTER( ALL( 'BOH Hours' ), [PO] = _p && [Date] >= _s && [Date] <= _e && [Date] <= MAX( 'BOH Hours'[Date] ) ) ) VAR _notinrangeHours = CALCULATE( SUM( 'BOH Hours'[AVG hours per month] ), FILTER( ALL( 'BOH Hours' ), [Date] <= MAX( 'BOH Hours'[Date] ) ) ) RETURN IF( _s = BLANK(), _notinrangeHours, IF( _e < MAX( 'BOH Hours'[Date] ), BLANK(), _inrangeHours ) )With the following result (Taken from my actual data so the PO number is different from sample)
- 2 years ago
you can write the final measure like this
Final = VAR _max = EOMONTH(CALCULATE(MAX('Data'[End date]),ALLEXCEPT(Data,Data[PO])),0) VAR _Result = SUMX( SUMMARIZE('Data','Data'[PO],Data[Start date],Data[End date]),[cumulative]) RETURN IF( MAX('Calendar'[Date])<=_max,_Result) - 2 years ago
pls try this
That's standard amortization. Please explain the cumulative part.
Sorry I misunderstood your message
This is the result I am looking for:
- Ashish_Mathur2 years ago
Super User
Hi,
PBI file attached.
Hope this helps.
- Yuiitsu2 years ago
Helper V
Thank you for answering my question!
Unfortunately the result is not what I am looking for.
Example if I filter to PO #303100010538 the date should start from March 2023 till Feb 2024 and the total cumulative contract Qty should be 100.
But your result both end in September 2023.
- Ashish_Mathur2 years ago
Super User
Check your own data carefully. For both PO's, the last date is Sep 2023.