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

Please help v-jianboli-msft  this is the last thing that is missing that is filtering using sale date for both carry forward and current year sale 

other than that the answers are correct please after this I will accept the solution 

I really appreciate your help v-jianboli-msft 

Please help

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
3 years ago

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.