Forum Discussion

Yuiitsu's avatar
Yuiitsu
Icon for Helper V rankHelper V
2 years ago
Solved

split 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...
  • Yuiitsu's avatar
    Yuiitsu
    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

    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

     

     

    • 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)

     

     

     

  • Ahmedx's avatar
    Ahmedx
    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)