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

sergiom sergiom · Accepted Answer · 2020-12-09T21:20:18

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.

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

2 Answers