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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sahildoshi
Helper I
Helper I

Power BI : Default Current Month & Previous Month Selection

Hi All,

I am working on monthly sales report which fetches its data from monthly invoice excel files as and when data available.

i.e June-2021 file will contain data of June sales data.

 

In this report, main visual is a table where current month and previous month sales comparisons by region. My report looks like below when user selects Jun-2021 and Jul-2021 from Month Slicer.

mom_sales.PNG

 

Above chart gives user the flexibility to compare any two month's sales.

 

Now what my users need is, keeping above month selection flexible, but by default month slicer selection should be Max month and Max month -1. i.e If Aug-2021 data is available then, once report data refreshes, month slicer should select Aug-2021 and Jul-2021. 

 

Now, from little googling I found out that I need below calculated column to achieve this. Can someone please help me derive "Month Year 2" column given that I have initial two columns.

mom_output.PNG

 

Please note that my data is not dependant on system date i.e though October-2021 is going on, I still might have data till September only. So my Current month is Maximum month from available data.

@amitchandak @GilbertQ 

1 ACCEPTED SOLUTION
richbenmintz
Solution Sage
Solution Sage

Hi @sahildoshi ,

The Following would have you add three new columns:

1. A deterministic Month Year Column to ensure you get the correct max month

- this I added into the source data I mocked up, the format should be yyyyMM

2. A Year Month Number that creates a sequence of months that allow us to get the max and subtract 1 for the previous month

YearMonthNumber = Left([MonthYear],4) * 12 -1 + RIGHT([MonthYear],2) 

3. The Column you requested

Month Year 2 = 
var _maxMonth = CALCULATE(max('test'[YearMonthNumber]), ALL('test'))
var _month = [MonthYear]
return
SWITCH(
    true(), 
    [YearMonthNumber] = _maxMonth, "Current Month",
    [YearMonthNumber] = _maxMonth-1, "Previous Month",
    [Year-Month]
)

 

See the screen shot below. displaying the results

 

richbenmintz_0-1633358538252.png

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

12 REPLIES 12
AntoineTRICHET
Resolver III
Resolver III

HI @sahildoshi 


Try the following formula for your calculated column :

MonthYearLast =
VAR currentMonth = LASTDATE(YourTable[MonthYear (date format)])
VAR previousMonth = DATEADD(currentMonth,-1,month)
VAR result = IF(AND(YEAR(YourTable[MonthYear (date format)]=YEAR(currentMonth),MONTH(YourTable[MonthYear (date format)]=MONTH(currentMonth)),"Current Month",
IF(AND(YEAR(YourTable[MonthYear (date format)]=YEAR(previousMonth),MONTH(YourTable[MonthYear (date format)]=MONTH(previousMonth)),"Previous Month",
FORMAT(YourTable[MonthYear (date format)],"Text"))
RETURN
result

Please accept it as solution if it solved your issue

richbenmintz
Solution Sage
Solution Sage

Hi @sahildoshi ,

The Following would have you add three new columns:

1. A deterministic Month Year Column to ensure you get the correct max month

- this I added into the source data I mocked up, the format should be yyyyMM

2. A Year Month Number that creates a sequence of months that allow us to get the max and subtract 1 for the previous month

YearMonthNumber = Left([MonthYear],4) * 12 -1 + RIGHT([MonthYear],2) 

3. The Column you requested

Month Year 2 = 
var _maxMonth = CALCULATE(max('test'[YearMonthNumber]), ALL('test'))
var _month = [MonthYear]
return
SWITCH(
    true(), 
    [YearMonthNumber] = _maxMonth, "Current Month",
    [YearMonthNumber] = _maxMonth-1, "Previous Month",
    [Year-Month]
)

 

See the screen shot below. displaying the results

 

richbenmintz_0-1633358538252.png

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @richbenmintz thank you for your reply.

I am getting below error when tried your switch formula

"SWITCH does not support comparing values of type True/False with values of type Number. Try using VALUE or FORMAT function to convert one of the values"

 

I am not sure why this error as none of my data type is True/False.

Hi @sahildoshi ,

 

Are you able to provide either sample data or a sample pbix file? 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Sorry I can't share either of it.

Can you paste the formula then



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Actually it is on the virtual machine, so I can't provide anything.
But I assure you, formula is exactly same as yours.

Hi @sahildoshi ,

 

Are you not able to copy and paste the formula code from the VM? I believe that the code is the same, however Without seeing the formula, and the name of your attributes is it really hard to troubleshoot this for you. 

The error seems to be pointing to the formula engine trying to compare the value of the switch condition to true(). so if the comparison does not return a true() or false() it will fail.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @richbenmintz I don't know what happened but it worked man!! It is working just fine now. 
I need a little favour from you, can you please help me how to determine the maximum and minimum value from the selection of Month_year 2.

 

For example, If Current month & Mar-21 is selected from the slicer, how do I determine Current Month is bigger value and Mar-21 is lesser. 

 

Let me know if you need separate question for it on community. Accordingly I'll accept your solution and close this thread.

Hi @sahildoshi ,

 

If I understand you can created the following measures

Max Selection = 
var _max = MAX([YearMonthNumber])
return 
CALCULATE(MAX(test[Month Year 2]), 'test'[YearMonthNumber] = _max)

Min Selection = 
var _min = Min([YearMonthNumber])
return 
CALCULATE(MAX(test[Month Year 2]), 'test'[YearMonthNumber] = _min)

 

results in the following results in card visuals

richbenmintz_0-1633374061727.png

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


thanks man!! worked like a charm

np @sahildoshi ,

 

glad to help!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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