Create filter for CURRENT MONTH, PREVIOUS MONTH and SAME MONTH LAST YEAR

[bilingual]

Hi i am creating a table, where i continually needs to compare the current month results with same month last year and last month.

I have created separate columns for PREVIOUS MONTH and SAME PERIOD LAST YEAR, but i am in doubt how to create a filter which includes both, can anybody help

[v-alq-msft]

Hi, @bilingual 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[Sujit_Thakur]

Dear @bilingual , 

There is a easy way , 

You can  do what is duplicate the column you Want visualize in your model . Name it as last year .

Then while adding those two columns in table , add respective filter on visuals from filter pane I.e for the orignal column add filter of date for this month . And for duplicate column which we named as last year add filter of last years month ..

This is very simple way 👍

Regards ,

Sujit Thakur 

Please give kudos if you like this creative and easy thinkinh and accept my post as solution so that other people like us can get this idea 👍👍

[v-alq-msft]

Hi, @bilingual 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Tab:

Test:

There is no relationship between two tables. You may create measures as below.

Current Month = 
IF(
    ISFILTERED(Test[Period]),
    IF(
            "Current Month" in DISTINCT(Test[Period]),
            CALCULATE(
                SUM(Tab[Value]),
                FILTER(
                    ALLSELECTED(Tab),
                    YEAR(Tab[Date])=YEAR(SELECTEDVALUE(Tab[Date]))&&
                    MONTH(Tab[Date])=MONTH(SELECTEDVALUE(Tab[Date]))
                )
            )
    )
)

Last Month = 
IF(
    ISFILTERED(Test[Period]),
    IF(
        "Last Month" in DISTINCT(Test[Period]),
        CALCULATE(
                SUM(Tab[Value]),
                FILTER(
                    ALL(Tab),
                    Tab[Date]>=EOMONTH(SELECTEDVALUE(Tab[Date]),-2)+1&&
                    Tab[Date]<=EOMONTH(SELECTEDVALUE(Tab[Date]),-1)
                )
        )
    )
)

Last Year Same Month = 
IF(
    ISFILTERED(Test[Period]),
    IF(
        "Last Year Same Month" in DISTINCT(Test[Period]),
        CALCULATE(
            SUM(Tab[Value]),
            FILTER(
                ALLSELECTED(Tab),
                YEAR(Tab[Date])=YEAR(SELECTEDVALUE(Tab[Date]))-1&&
                MONTH(Tab[Date])=MONTH(SELECTEDVALUE(Tab[Date]))
            )
        )
    )
)

Result:

Best Regards

Allan

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

[amitchandak]

@bilingual , If need a column in calendar Table you can have like this

Switch( True(),
eomonth([Date],0) >= eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0) >= eomonth(Today(),0),"This Month" ,

eomonth([Date],0) >= eomonth(Today(),-12),"Last Year Same Month" ,
Format([Date],"MMM-YYYY")
)

But in case you nee measures you can use time intelligence with date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Also refer : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[bilingual]

Thanks al lot Amit, i have a little problem as the data is aggregated only in months not dates, so the formula is not working, could you help with a workaround for the Switch formula?

[v-alq-msft · Accepted solution]

Hi, @bilingual 

If you want data to be aggregated only in months, you may create calculated columns and measures as below. The pbix file is attached in the end.

Calculated column:

Year = YEAR(Tab[Date])

Month = MONTH(Tab[Date])

Measure:

Current Month = 
IF(
    ISFILTERED(Test[Period]),
    IF(
            "Current Month" in DISTINCT(Test[Period]),
            CALCULATE(
                SUM(Tab[Value]),
                FILTER(
                    ALLSELECTED(Tab),
                    YEAR(Tab[Date])=SELECTEDVALUE(Tab[Year])&&
                    MONTH(Tab[Date])=SELECTEDVALUE(Tab[Month])
                )
            )
    )
)

Last Month = 
IF(
    ISFILTERED(Test[Period]),
    IF(
        "Last Month" in DISTINCT(Test[Period]),
        IF(
            SELECTEDVALUE(Tab[Month])>1,
            CALCULATE(
                    SUM(Tab[Value]),
                    FILTER(
                        ALL(Tab),
                        Tab[Year]=SELECTEDVALUE(Tab[Year])&&
                        Tab[Month]=SELECTEDVALUE(Tab[Month])-1
                    )
            ),
            IF(
                SELECTEDVALUE(Tab[Month])=1,
                CALCULATE(
                    SUM(Tab[Value]),
                    FILTER(
                        ALL(Tab),
                        Tab[Year]=SELECTEDVALUE(Tab[Year])-1&&
                        Tab[Month]=12
                    )
                )
            )
        )
    )
)

Last Year Same Month = 
IF(
    ISFILTERED(Test[Period]),
    IF(
        "Last Year Same Month" in DISTINCT(Test[Period]),
        CALCULATE(
            SUM(Tab[Value]),
            FILTER(
                ALLSELECTED(Tab),
                Tab[Year]=SELECTEDVALUE(Tab[Year])-1&&
                Tab[Month]=SELECTEDVALUE(Tab[Month])
            )
        )
    )
)

Result:

Best Regards

Allan

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

[bilingual]

Hi Allan, sorry the late reply due to summer vacation, i really appreciate your help!

I have tried to use your example to work with my dataset, but it does not seem, i can make it work, could you be so kind to help me? - i have attached a very simple version of the data aligned to your example.

Last month has some issues with the results as it seems to aggregate the results instead of showing last month

Link to file : Power BI file with added data 

[Ashish_Mathur]

Hi,

Share your sample data.