slicer selection to next month for substraction results

[Anonymous]

Hi All, i have the slicer with month names like July,Aug,Sep,Oct.  in one of the visual im showing the change which gets calculated by substracting numbers like count of Aug - count of july i want the value to start appear on Aug selection and July should show 0 as there is no comparison for July-June by default the visual showing value in july selection which should actually appear on Aug selection how should i make second selection as first selection to show subtractiong results

[Icey]

Hi @Anonymous ,

I'm not very clear about your requirements. Please check if this is what you want:

substraction result = 
VAR ThisMonthSales =
    SUM ( 'Table'[Sales] )
VAR LastMonthSales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date]
                >= EOMONTH ( MAX ( Dates[Date] ), -2 ) + 1
                && 'Table'[Date] <= EOMONTH ( MAX ( Dates[Date] ), -1 )
        )
    )
RETURN
    IF (
        ISFILTERED ( Dates[MonthName] ),
        IF ( ISBLANK ( LastMonthSales ), 0, ThisMonthSales - LastMonthSales ),
        0
    )

Best Regards,

Icey

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

[Anonymous]

Hi @Icey 

you solution seems pretty close to what im looking for. Not sure why im stuck here and not able to get the right result.

 Just to mentioned i have separate table for each month and each table have data of single month only . i applied your measure as well but not sure why it is giving 0 result for each month. 

please see below google doc where i captured some data -

https://docs.google.com/spreadsheets/d/1rB9nbYQYgh9HDWlBRn9XrsYFan_0xyMmVCHNrJUUENk/edit#gid=9256603... 

[Icey · Accepted solution]

Hi @Anonymous ,

If you have separate table for each month and they are the same data structure, it is suggested to append your tables. And then, use the measures above.

If you don't want to append them, try this:

substraction result =
VAR ThisMonthSales =
    SWITCH (
        SELECTEDVALUE ( Dates[Month] ),
        7, SUM ( '2019/7/1'[Sales] ),
        8, SUM ( '2019/8/1'[Sales] ),
        9, SUM ( '2019/9/1'[Sales] ),
        10, SUM ( '2019/10/1'[Sales] )
    )
VAR LastMonthSales =
    SWITCH (
        SELECTEDVALUE ( Dates[Month] ),
        7, 0,
        8, SUM ( '2019/7/1'[Sales] ),
        9, SUM ( '2019/8/1'[Sales] ),
        10, SUM ( '2019/9/1'[Sales] )
    )
RETURN
    IF (
        ISFILTERED ( Dates[MonthName] ),
        IF ( ISBLANK ( LastMonthSales ), 0, ThisMonthSales - LastMonthSales ),
        0
    )

BTW, .pbix files attached.

Best regards

Icey

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

[Anonymous]

Hi @Icey 

thanks for quick help, it works

[Mariusz]

Hi @Anonymous 

Can you provide some screenshots that explain your problem?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn