YTD Plan,actual, variance and acheivement from the Monthly Values based on financial month slicer

[NadeemAhamed]

Dear Community.

I am trying to calculate the cumualtive (YTD) from the Monthly values based on financial month slicers. 

Input excel file. 

If user selected Apr month, then YTD will be calculate till month Apr

If user selected Jun month, then YTD will be calculate from Apr to Jun month

then i have to calculate YTD variance (Actual -Plan)  and YTD Achievement (Actual/Plan)

Expected result as below. 

I have achieved the Monthly Variance,Achievment, but not able to YTD Plan,Actual, Variance and Acheivement 

Kindly revert back if any questions and expaination. 

I have added the input file for your reference. 

https://toyotsu.box.com/s/tyciqj4tenkes43ldeyrzy6o8bkx6u8x

[v-fenling-msft]

Hi,@NadeemAhamed 

I am glad to help you.  

According to your description, you want to calculate YTD Plan, actual, variance and acheivement from the Monthly Values based on financial month slicer?

If I understand you correctly, then you can refer to my solution. 

I am sorry to tell you that after using the slicer, only the data that meets the criteria is filtered out. For example, if the slicer selects the month of April, then only data from April will be displayed in the visuals. In short, only the data filtered by the slicer can be displayed in the visuals, it is not possible to control the calculation and realize your needs by using the slicer.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

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

[NadeemAhamed · Accepted solution]

@v-fenling-msft 

Thank you fo your valuable time to read and understand my requirement. 

I would to like to inform that, i have acheived the result by using the finanical month slicers. 

here is the measures which i have used. 

Measures for YTD(Cumulative) Plan: 

Cumulativeplan =

VAR SelectedMonth = SELECTEDVALUE('Financial Month'[Month])

VAR SelectedMonthNumber = CALCULATE(SUM('Financial Month'[Order]),'Financial Month'[Month]=SelectedMonth)

RETURN

CALCULATE (

    SUM(Sheet1[Plan]),

    FILTER (

        ALL('Financial Month'),

         'Financial Month'[Order] <= SelectedMonthNumber

    )

)

Measures for YTD Actual: 

Cumulativeactual =

VAR SelectedMonth = SELECTEDVALUE('Financial Month'[Month])

VAR SelectedMonthNumber = CALCULATE(SUM('Financial Month'[Order]),'Financial Month'[Month]=SelectedMonth)

//MAXX(FILTER('Financial Month', 'Financial Month'[Month_Name]= SelectedMonth),'Financial Month'[Order])

RETURN

CALCULATE (

    SUM(Sheet1[Actual]),

    FILTER (

        ALL('Financial Month'),

         'Financial Month'[Order] <= SelectedMonthNumber

    )

)

Measures for Varaince: 

cumulative_Variance =

[Cumulativeactual]-[Cumulativeplan]

Measures for Acheivement: 

cumulative_Achievement =

VAR C_Actual = [Cumulativeactual]

VAR C_Plan = [Cumulativeplan]

VAR C_Achie=

DIVIDE([Cumulativeactual],[Cumulativeplan])

RETURN

FORMAT(C_Achie,"#.##%")