Measures in Power BI Calculation

Hi Anonymous ,

Please try:

Carry forward Sale2 = 
VAR _a =
    MAXX ( 'Calendar', [Date] )
VAR _b =
    MINX ( 'Calendar', [Date] )
VAR _c =
    SELECTCOLUMNS ( 'Table', "State", [State] )
var _e = SELECTCOLUMNS('Table',"Date",[Sale Date])   
VAR _d =
    SUMX (
        FILTER ( ALL ( 'Table' ), [State] IN _c && [Sale Date] <= _a &&[Sale Date] in _e),
        SWITCH (
            TRUE (),
            NOT([Sale Date] in SELECTCOLUMNS('Table',"Date",[Sale Date])),0,
            DATEDIFF ( [Sale Date], _a, YEAR ) >= 2, 0,
            DATEDIFF ( [Sale Date], _a, YEAR ) <= 0, 0,
            DATEDIFF ( [Sale Date], _b, MONTH ) <= [Service Period  (in months)],
                DIVIDE ( [Total Sale], [Service Period  (in months)] )
                    * ( [Service Period  (in months)] - DATEDIFF ( [Sale Date], _b, MONTH ) ),
            0
        )
    )
RETURN
    _d

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I made some changes to   and this works perfectly

Carry forward Avoidance =

VAR _a =

    MAXX ( 'Calendar', [Date] )  

VAR _b =

    MINX ( 'Calendar', [Date] )  

VAR _c =

    SELECTCOLUMNS ( 'Data', "Catg", [Catg] )

var _e = SELECTCOLUMNS('Data',"Date",[SaleDate])  

VAR _d =

    SUMX (                          

        FILTER ( ALL ( 'Data' ), [Catg] IN _c && [SaleDate] <= _a && Data[SaleDate] in _e ),

        SWITCH (

            TRUE (),

             NOT([Saledate] in SELECTCOLUMNS('Data',"Date",[Saledate])),0,

            DATEDIFF ( [Saledate], _a, YEAR ) = 1,

                SWITCH (

                    TRUE (),    

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) < Data[ServiceMonths]

                    &&

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) + 12 >= Data[ServiceMonths]

                     &&

                                        Data[ServiceMonths] - (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) <= 12

,

                    Data[ServiceMonths] - (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ),

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) < Data[ServiceMonths]

                    &&

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) + 12 >= Data[ServiceMonths]

                     &&

                                        Data[ServiceMonths] - (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) >12

,

                    12,

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1) + 12  < Data[ServiceMonths] , 12 ,

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1) >= Data[ServiceMonths] , 0 ,0)

                ,

            DATEDIFF ( [Saledate], _a, YEAR ) = 2,

                SWITCH (

                    TRUE (),

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12  < Data[ServiceMonths]

                    &&

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 + 12 <=  Data[ServiceMonths]

                    , 12,

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12  < Data[ServiceMonths]

                    &&

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 + 12 >= Data[ServiceMonths]

                    , Data[ServiceMonths]-((DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 ),

                    (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)>=Data[ServiceMonths],0,0),0)

                    * DIVIDE(Data[AvoidanceSale],Data[ServiceMonths]))

return _d

Current year Avoidance sale =

VAR _a =

    MAXX ( 'Calendar', [Date] )

VAR _b =

    SUMX (

        FILTER('Contracts',Contracts[Saledate]<=_a&&Contracts[Saledate]>=MINX('Calendar',[Date])),

        DIVIDE ( Contracts[AvoidanceSale], [Service Months] )

            * (

                MINX (

                    { DATEDIFF ( 'Contracts'[Saledate], _a, MONTH ), [Service Months] },

                    [Value]

                )

                    + IF (

                        MINX (

                            { DATEDIFF ( 'Contracts'[Saledate], _a, MONTH ), [Service Months] },

                            [Value]

                        ) = [Service Months],

                        0,

                        1

                    )

            )

    )

RETURN

    _b