Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Measures in Power BI Calculation

Hello All , This is a question which has been a problem for atleast a month . I am using Power BI for more than a year. But I am stuck here and any help will be highly appreciated Following is the ...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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