Forum Discussion

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

split value equally into months and cumulative

 

Sorry I think I posted this earlier but  I cannot find it anywhere.

So I repost again

 

Need help with my measure here:

 

 

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?

 

I cannot upload Pbix  so I am not able to show sample but if needed here is a part of my table here.

 

1 Reply