Calculate Current and Previous month's Value based on slicer selection in power bi

Question

I have two tables called

Main Table contains the following data(Pic) and
Date table created using the dates from the Main table.

I want to calculate two fields/measures based on Date slicer selection from the Date Table

current month's revenue
previous month's revenue

Example: If I selected the 4th Month then it should sum distinct revenue of client A and B for the 4th month as current_month_revenue and Sum distinct revenue of A and B for 3rd month as previous_month_revenue.

I tried writing the following Measure to Calculate current_month_revenue and it is working fine but it is not giving the correct result for Previous_month_revenue. I am getting the same value for the Previous month as well.

'Measure Previous Month Revenue' =
IF (
    ISFILTERED ( 'Date'[Year_Month] ),
    VAR myTable =
        SUMMARIZE (
            'Main Table',
            'Main Table'[ClientName],
            'Main Table'[Mon],
            'Main Table'[Revenue]
        )
    RETURN
        CALCULATE (
            SUMX (
                myTable,
                'Main Table'[Revenue]
            ),
            FILTER (
                'Main Table',
                'Main Table'[Mon]
                    = SELECTEDVALUE ( 'Date'[Month] - 1 )
            )
        ),
    VAR myTable =
        SUMMARIZE (
            'Main Table',
            'Main Table'[Revenue],
            'Main Table'[Mon],
            'Main Table'[Revenue]
        )
    RETURN
        SUMX (
            FILTER (
                myTable,
                'Main Table'[Mon]
                    = MONTH (
                        TODAY ()
                    ) - 1
            ),
            'Main Table'[Revenue]
        )
)

Desired Output

If 4th month is selected

Current Moth revenue = 100 + 200 = 300
Previous Month = 100+200 = 300

In this case, both are the same but in actual data, revenue is different for each month.

It would help answerers to present your data in text form rather than as an image. — Alexis Olson, Dec 09 '20 at 21:04

score 1 · Answer 1 · answered Dec 09 '20 at 21:19

CALCULATE does not affect variables that you've already defined, so FILTER does nothing to the first SUMX. See this related post for a bit more detail.

I'd suggest writing the measure much more simply. Something like this:

Previous Month Revenue =
VAR PrevMonth =
    SELECTEDVALUE (
        'Date'[Month],
         MONTH ( TODAY () )
    ) - 1
RETURN
    CALCULATE (
        SUM ( 'Main Table'[Revenue] ),
        'Main Table'[Mon] = PrevMonth
    )

Yes ..Used it along with the above to get the desired result. — Bond, Dec 10 '20 at 19:57

sergiom · Accepted Answer · 2020-12-10T14:18:17.597

I see from the code that the 'Date' table has a numeric column called Month, that I assume to be of the same type as the Mon column in your 'Main Table'.

Also, since in the 'Main Table' there is no Year, I assume that the Year is not to be considered.

From the slicer over the Date table we can directly get the selected 'Date'[Month] using SELECTEDVALUE(). As default parameter we use the current month obtained by the TODAY() function.

Then we obtain the Previous Month subtracting one from the Selected Month and we can use it to slice the table grouped by CustomerName, Mon and Revenue. Grouping is needed to remove duplicate Revenues for the same customer on the same month and is implemented using SUMMARIZE()

As a final step we can aggregate the 'Main Table'[Revenue] of the filtered and grouped table using SUMX.

'Measure Previous Month Revenue' = 
VAR CurrentMonth =
    MONTH(
        TODAY()
    )
VAR SelectedMonth =
    SELECTEDVALUE(
        'Date'[Month],
        CurrentMonth
    )
VAR PrevMonth = SelectedMonth - 1   
VAR MyTable =
    CALCULATETABLE(
        SUMMARIZE(
            'Main Table',
            'Main Table'[ClientName],
            'Main Table'[Mon],
            'Main Table'[Revenue]
        ),
        'Main Table'[Mon] = PrevMonth,
        REMOVEFILTERS( 'Date' )
    )
VAR Result =
    SUMX(
        MyTable,
        'Main Table'[Revenue]
    )
RETURN
    Result

The same code but for the Previus Month calculation can be written for the current month

'Measure Current Month Revenue' = 
VAR CurrentMonth =
    MONTH(
        TODAY()
    )
VAR SelectedMonth =
    SELECTEDVALUE(
        'Date'[Month],
        CurrentMonth
    )
VAR MyTable =
    CALCULATETABLE(
        SUMMARIZE(
            'Main Table',
            'Main Table'[ClientName],
            'Main Table'[Mon],
            'Main Table'[Revenue]
        ),
        'Main Table'[Mon] = SelectedMonth,
        REMOVEFILTERS( 'Date' )
    )
VAR Result =
    SUMX(
        MyTable,
        'Main Table'[Revenue]
    )
RETURN
    Result

A better solution could be implemented setting a relationship between the 'Date' table and 'Main Table'. Depending on the business requirement, it could be possilbe to use a Date table at the month level granularity, with a YearMonth column instead of Mon, or at the Day level, with a Date column instead of the Mon column.

Using the above, The measure is giving correct results of nothing is selected on the slicer, but I am getting blank if I am selecting some other month. Note: My slicer is created using the Year-Month field from Date Table Yes, There is a relationship between Date Table and Main table are connected on Date Key — Bond, Dec 10 '20 at 11:10
OK, so there is a Date column on the 'Main Table' and a relationship between that column and 'Date'[Date]. Then this measure can be change by removing the filter over 'Date' — sergiom, Dec 10 '20 at 14:15
Yay !! it's working, Thanks @sergiom Appreciate your help. REMOVEFILTERS( 'Date' ) was the missing piece. Can you please help me understand why we used this line? — Bond, Dec 10 '20 at 19:55
We need to remove the filtering introduced by the relationship between the Date table and the Main Table. This because the selection in the slicer propagates from the Date table to the Main Table follwing the relationship. But we want to apply a filter over the Mon column instead, therefore we must get rid of this filter, and REMOVESFILTERS( 'Date' ) does this: it removes the filter that propagates from the Date table to the Main Table. The effect of REMOVEFILTERS is local to the CALCULATE function where it is appled. After the filter is removed, the filter over Mon colum is applied. — sergiom, Dec 10 '20 at 20:18
Knowing that the model contains a relationship between the Date table and the Main Table table would allow to write a measure leveraging the time intelligence functions, with no need to use the Mon column. But this would be a different Question with a descrition of the model, the granularit of the Date table and the confirmation that the Mon column contains the same month of the Date used for the relationship. — sergiom, Dec 10 '20 at 20:24
Ahh... Thanks for the explanation, helped in writing other formulas. — Bond, Dec 11 '20 at 19:24

Calculate Current and Previous month's Value based on slicer selection in power bi

2 Answers2