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
lbendlin
Super User
2 years agoSo you want cumulative amortization? What is the expected outcome based on your sample data?
Yuiitsu
Helper V
2 years agoHi
If I filter to 303100010538 it still show like this:
If filter to 608800001452
- lbendlin2 years ago
Super User
That's standard amortization. Please explain the cumulative part.
- Yuiitsu2 years ago
Helper V
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.