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
Ashish_Mathur Thank you.
I think yes it can be done with 2 seperate table.
I have solved the issue by creating 2 extra column.
1.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
2. Create a column to count the average hours per month
AVG hours per month = DIVIDE('BOH Hours'[Contract Qty],'BOH Hours'[Month Diff])
3. 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 )
)
I feel the sample data i provided is not good enough so everyone has a hard time to help me.
I will go learn how I can upload pbix using one drive next time when I have question.
Once again thank you everyone
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)
- Yuiitsu2 years ago
Helper V
This works perfectly too!
- Yuiitsu2 years ago
Helper V
Ahmedx hi!
I notice something that the column how many months have passed is not accurate if it is over 12 months.
I changed the 1st PO end date to 6/1/2024 and the months passed should be 16 but it still show 12 here.
I notice this when i use your syntax on my original data.
(Count month is equal to your how many months have passed measure)
(Month Diff a column i create to check the accuracy of month passed)
Please advise what should I edit in the month passed syntax to resolve this?
how many months have passed = CALCULATE( SUMX(SUMMARIZE('Data','Data'[PO],Data[Start date],Data[End date]), CALCULATE ( DISTINCTCOUNT('Calendar'[Month]), INTERSECT ( VALUES ( 'Calendar'[Date] ), DATESBETWEEN ( 'Calendar'[Date], 'Data'[Start date], 'Data'[End date] ) ) )),'Calendar'[Date]<= MAX('Calendar'[Date]),REMOVEFILTERS('Calendar')) - Ahmedx2 years ago
Super User
pls try this
- Yuiitsu2 years ago
Helper V
Sorry Ahmedx can you help me with something else?
My date is abit different from yours so can you amend my date to have the new column that you added?
Date = ADDCOLUMNS ( CALENDAR (DATE (1992, 1, 1), DATE (2099, 12, 31)), "Year", YEAR([Date]), "Month", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "MonthNumber", MONTH([Date]) )