Show previous month values based on slic

[SG-01]

Hi there,

I use the following measure to summarize the revenue for the current month:

CurrentMonthRevenue = CALCULATE(SUM(Order[Revenue]), FILTER(Order, MONTH(Order[Date])=MONTH(TODAY()))

And this measure for the previous month:

PreviousMonthRevenue= var current_month = month(TODAY()) return CALCULATE(SUM(Order[Revenue]), FILTER(Order, MONTH(Order[Date])= current_month -1))

However, this does not work when I am using a date slicer with the month of the years in it. I use a separate calender table.

How can I change this that the previous month revenue will be summarize based on the selected months in the slicer?

Now it calculates it for this month (November) and October, but if June and July are chosen in the slicer it has to calculate it for those months.

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @SG-01 ,

According to your description, it's because in the formula you provided, havn't take the Date slicer into consideration. Here's my solution.

Order table:

Date table:

Create two measures.

CurrentMonthRevenue =
CALCULATE (
    SUM ( 'Order'[Revenue] ),
    FILTER (
        'Order',
        MONTH ( 'Order'[Date] )
            = IF (
                ISFILTERED ( 'Date'[Month] ),
                SELECTEDVALUE ( 'Date'[Month] ),
                MONTH ( TODAY () )
            )
    )
)

PreviousMonthRevenue =
VAR current_month =
    IF (
        ISFILTERED ( 'Date'[Month] ),
        SELECTEDVALUE ( 'Date'[Month] ),
        MONTH ( TODAY () )
    )
RETURN
    CALCULATE (
        SUM ( 'Order'[Revenue] ),
        FILTER ( 'Order', MONTH ( 'Order'[Date] ) = current_month - 1 )
    )

Result:
If there isn't month been selected in the slicer, it will return the current month and corresponding previous month.

If other month is selected in the slicer, it will return the selected month and corresponding previous month.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[djurecicK2]

Hi @SG-01 ,

 What is the name of your date table? You need to use the ALL function in last month's measure to escape from the slicer filtering.

Something like this (adapt to your table and field names). Also need to include year to set the current year

CALCULATE(SUM(Order[Revenue]), FILTER(ALL(Datetable), Datetable[MonthNumnber])= current_month -1))

Please consider accepting as solution if this answers the question.

[SG-01]

No, I want to INCLUDE the slicer filtering in my measure. Based on the chosen months in the slicer the revenue for the current and previous months needs to be calculated. 

[sudhav]

you can use relative date in slicer and select last 1 calender months... then you will by default prior month revenue when ever you can open that report,, if you want current month means select this month in same slicer....