Forum Discussion
Yuiitsu
Helper V
2 years agosplit value equally into months and cumulative
Hey experts I need your help on my measure below. I thought I have posted this question but I cannot find it anywhere. I made a measure like this to split the contract qty equally between the co...
- 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
Ahmedx
Super User
2 years agoBased on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Final =
Measure = SUMX(
SUMMARIZE('Data','Data'[PO],Data[Start date],Data[End date]),[cumulative])
Yuiitsu
Helper V
2 years agoThank 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)