Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aanyoti
Helper I
Helper I

Previous Month Measure to calculate total for the last month

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 

 

aanyoti_0-1625720478667.png

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

aanyoti_1-1625720897099.png

 

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])
)

 

 

1 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
 

View solution in original post

9 REPLIES 9
v-luwang-msft
Community Support
Community Support

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 :

vluwangmsft_0-1626157964951.png

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)

vluwangmsft_1-1626158031124.png

vluwangmsft_2-1626158159654.png

 

You could download my pbix file if you need.

Wish it is helpful for you!

 

 

Best Regards

Lucien

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])))

 

aanyoti_0-1626211957045.png

 

 

selimovd
Super User
Super User

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
 

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
 

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

 

aanyoti_1-1626212321613.png

 

 

 

 

Hi @aanyoti ,

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

 

 

Best Regards

Lucien

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
 

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

 

aanyoti_0-1626254821307.png

 

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?

 

MonthMTDPrevious Month_2Month Total(Required Measure)
January3410341
February8341349
March38352
April123364
May612370
June116381
July411385

 

Thanks again for all the help.

 

Hi @selimovd ,

 

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

 

MonthMTDPrevious Month_2Month Total(Required Measure)
January3410341
February8341349
March38352
April123364
May612370
June116381
July411385

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.