Previous Month Measure to calculate total for the last month

[aanyoti]

Hello, hoping I can get some guidance on getting this measure correctly.

I have the below table (sample data) and have created a Month to Date measure that populates the line and stacked column visual below 

MTD = TOTALMTD(COUNT(Zones[Status]),Zones[Date Active])

I want to create a measure that will count the total cells for the previous month. This is so that I can show per month on the visual, total cells for the previous month along with cells added in the current month.

I attempted to create the below measure using PREVIOUS MONTH DAX function, but this is not working.

Previous Month = CALCULATE(
                    COUNT(Zones[Status]),
                    PREVIOUSMONTH(Zones[Date Active])
)

[v-luwang-msft]

Hi @aanyoti ，

Try the following measure:

Previous Month2 = 
CALCULATE(
    COUNT( Zones[Status] ),
     FILTER(ALL(Zones),DATEADD(Zones[Date Active],1,MONTH)=MAX(Zones[Date Active])))

Final get :

I also test your measure,but it only applies to the following cases，when I use the Month with the measure ,it will return me blank (the measure @selimovd  provided I also return a blank result,you can see the screenshots below)

You could download my pbix file if you need.

Wish it is helpful for you!

Best Regards

Lucien

[aanyoti]

Hi @v-luwang-msft ,

Thanks for the response and details about the measure. I have tried it and its still not giving me the desired result. See below visual outputs, not exactly sure where its going wrong. I reviewed your pbix file and have done it exactly as you did. Where am I missing it?

Previous Month = CALCULATE(
                    COUNT(Zones[Status]),
                    FILTER(ALL(Zones), DATEADD(Zones[Date Active],1,MONTH) = MAX(Zones[Date Active])))

[selimovd]

Hey @aanyoti ,

PREVIOUSMONTH is the wrong function. This will only return the previous month. You want to change the date in the filter context. For that you could use DATEADD:

Previous Month = 
CALCULATE(
    COUNT( Zones[Status] ),
    DATEADD ( Zones[Date Active], -1, MONTH )
)

Be aware that you should use a proper Date dimensional table in order for the time intelligence functions to work properly. And also to do proper analysis.

Check out how to do that:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd]

Hey @aanyoti ,

did it work with the DATEADD function?

I'm curious if you could solve this issue 🙂

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[aanyoti]

Hi @selimovd ,

Unfortunately it did not, the measure does make sense to me, but it returned blanks. See below. Not sure how its returning a total either. 

Previous Month_2 = 
CALCULATE(
    COUNT( Zones[Status] ),
    DATEADD ( Zones[Date Active], -1, MONTH )
)

[v-luwang-msft]

Hi @aanyoti ,

Could you pls remove your confidential data  then share your pbix file?

Best Regards

Lucien

[selimovd · Accepted solution]

Hey @aanyoti ,

as I wrote you need a proper date table. This date table you also have to refer in the DATEADD function. Otherwise the time intelligence functions cannot work.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[aanyoti]

Got it now, used the date in the date table for the DATEADD function and its working-😅

Need help with one last issue, how do I create a measure that will count the sum of the previous month_2 measure with the MTD and place it in the next months row as below?

Month	MTD	Previous Month_2	Month Total(Required Measure)
January	341	0	341
February	8	341	349
March	3	8	352
April	12	3	364
May	6	12	370
June	11	6	381
July	4	11	385

Thanks again for all the help.

[aanyoti]

Hi @selimovd ,

Could you assist with a measure to achieve the below column called Month total? Would be very grateful.

Month	MTD	Previous Month_2	Month Total(Required Measure)
January	341	0	341
February	8	341	349
March	3	8	352
April	12	3	364
May	6	12	370
June	11	6	381
July	4	11	385