cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

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

1 ACCEPTED SOLUTION

Accepted Solutions

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:

x1.png

 

Best Regards

Allan

 

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

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

Community Support
Community Support

Hi, @bilingual 

 

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

Tab:

a1.png

 

Test:

a2.png

 

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:

a3.png

 

a4.png

 

Best Regards

Allan

 

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

Hi,

Share your sample data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

x1.png

 

Best Regards

Allan

 

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

View solution in original post

Impactful Individual
Impactful Individual

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 👍👍

Community Support
Community Support

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

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 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors