Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

 

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.

 

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 

Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi Anonymous ,

    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

     

     

12 Replies

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi Anonymous ,

    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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

      • richbenmintz's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        5 years ago

        Hi Anonymous ,

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous 


    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