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
Sorry I misunderstood your message
This is the result I am looking for:
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.
- Yuiitsu2 years ago
Helper V
I am referring to the start and end date of the contract.
Date PO Contract Qty Hours used Start date End date March 2023 303100010538 100 March 2023 February 2024 April 2023 303100010538 100 6 March 2023 February 2024 May2023 303100010538 100
20 March 2023 February 2024 June2023 303100010538 100 12 March 2023 February 2024 July 2023 303100010538 100 March 2023 February 2024 August 2023 303100010538 100 March 2023 February 2024 September 2023 303100010538 100 3 March 2023 February 2024 March 2023 608800001452 250 March 2023 December 2023 April 2023 608800001452 250 March 2023 December 2023 May2023 608800001452 250 March 2023 December 2023 June2023 608800001452 250 20 March 2023 December 2023 July 2023 608800001452 250 60 March 2023 December 2023 August 2023 608800001452 250 12 March 2023 December 2023 September 2023 608800001452 250 March 2023 December 2023