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 contract start and end date:

 

 

 

AVG Contract Hours = 
VAR _MONTHSTART =
    MIN ( 'Date'[Date] )
VAR _datediff = 
    VAR startDate = 
        CALCULATE( MIN( 'BOH Hours'[Start] ),
        ALLEXCEPT( 'BOH Hours', 'BOH Hours'[PO] )
)
    VAR endDate = 
        CALCULATE( MAX( 'BOH Hours'[End] ),
        ALLEXCEPT( 'BOH Hours', 'BOH Hours'[PO] )
)
RETURN
    DATEDIFF(startDate, endDate, MONTH)+1
VAR _ActualConQTY =
    MINX(
        FILTER(
            SELECTCOLUMNS(
                ALLSELECTED('BOH Hours'),"index",'BOH Hours'[Index],"Min",MINX(
                    FILTER(
                        'BOH Hours','BOH Hours'[PO]=EARLIER('BOH Hours'[PO])),
                        'BOH Hours'[Contract Qty])),[index]=MAX('BOH Hours'[Index])),[Min])
VAR _AVG =
    DIVIDE (_ActualConQTY, _datediff )
RETURN
    IF (
        _MONTHSTART >= MAX ( 'BOH Hours'[Start] )
            && _MONTHSTART < MAX ( 'BOH Hours'[End] ),
        _AVG
    )

 

 

First I need to find the number of months between the start and end date using DATEDIFF.

Next I need to find the actual Contract Qty (Its not the SUM of all the same PO number)

Then I divide the Contract Qty and DATEDIFF to get the avarage per month.

 

Result is below but I need it to be cumulative. What should I do to make it cumulative?

 

 

Example of how my raw data looks like.

DatePOContract QtyHours usedStart dateEnd date
March 2023303100010538100 March 2023February 2024
April 20233031000105381006March 2023February 2024
May2023303100010538

100

20March 2023February 2024
June202330310001053810012March 2023February 2024
July 2023303100010538100 March 2023February 2024
August 2023303100010538100 March 2023February 2024
September 20233031000105381003March 2023February 2024
March 2023608800001452250 March 2023December 2023
April 2023608800001452250 March 2023December 2023
May2023608800001452250 March 2023December 2023
June202360880000145225020March 2023December 2023
July 202360880000145225060March 2023December 2023
August 202360880000145225012March 2023December 2023
September 2023608800001452250 March 2023December 2023
Need Help
Tutorial Requests
  • 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)

     

     

18 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    So you want cumulative amortization?  What is the expected outcome based on your sample data?

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

      Hi

       

      If I filter to 303100010538 it still show like this:

       

      If filter to 608800001452

       

       

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        That's standard amortization. Please explain the cumulative part.

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    Based 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's avatar
      Yuiitsu
      Icon for Helper V rankHelper V
      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)