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
Anshu_Joshi
Frequent Visitor

Current Month vs Previous Months

Hi Team, 

Here is the task, I have 6 Months data, January to June, I have to show the number of customers in the current month vs total number of customers from January to May.

I'm trying to create a measure but that is not working as expected. 

I appreciate your suggestions.

 

Current Month Total Customer = CALCULATE(COUNT('Customer list'[Customer Name]), FILTER(ALL('Customer list'), [Month] = MONTH(TODAY())))
 
Previous Months Total Customer = CALCULATE(COUNT('Customer list'[Customer Name]), FILTER(ALL('Customer list'), [Month] < MONTH(TODAY())))
 
Thanks in  Advance!
3 REPLIES 3
mahenkj2
Solution Sage
Solution Sage

Hi @Anshu_Joshi 

 

You said, 'not working as expected', so what's wrong? Result is incorrect or there are some errors?

 

Your Month column is text amd you are comparing it with MONTH(TODAY())) which is a number output. At first convert month name to month number.

 

Then your both formula seems to work well:

 

mahenkj2_0-1686733378810.png

 

Hope it helps.

Kishore_KVN
Super User
Super User

Hello @Anshu_Joshi ,

 

I believe you are using Month from different table. If that is not the case, then your formula should work. I tried to create same measures at my end and they are working. Can you please share the same data and the outputs you are getting. 

 

In my case you can see below screenshots for excel calculation and Power BI outputs as well:

sum of sales from 1st Jan to 31 May in Excel is as below which is 7982:

Kishore_KVN_0-1686725048336.png

Measure and output in Power BI are as below:

Kishore_KVN_1-1686725127115.png

Current Month as per excel is 710:

Kishore_KVN_2-1686725166290.png

Current Month as per Power BI:

Kishore_KVN_3-1686725211626.png

 

If Previous Months DAX is not working then you can use it as below as well:

Sales Before Current Month = CALCULATE(SUM(Data[Sales]),ALL(Data),FILTER(Data,Data[Month]<MONTH(TODAY())))

 

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

 

 

 

 

Hello @Kishore_KVN ,

Thank you for the reply.

 

Actually I'm having a date column from which I created a Month column.

I don't have numbers to sum but Customers name to count.

 

Anshu_Joshi_0-1686725984013.png

For the above Image I used this Measure: 

Current Month Total Customer = CALCULATE(COUNT('Customer list'[Customer Name]), FILTER(ALL('Customer list'), [Month] = MONTH(TODAY())))
and 
Previous Months Total Customer = CALCULATE(COUNT('Customer list'[Customer Name]), FILTER(ALL('Customer list'), [Month] < MONTH(TODAY())))

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors