Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Previous month dynamic column

Hi,

 

I have included a picture of the table below that I'm working with. Essentialy what I want is a Column that says "TRUE" when it is the previous month dynamically and "FALSE" when it isnt. I can do this through Power Query M, but. for my specific case when the month is January as it is now I want the previous month to be "TRUE" for December. Ignore the date column itself as that doesnt actually matter in this case.Would anyone know how to achieve this dynmaically through DAX?

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    is this what you want?

    Column = 
VAR monthnumber=SWITCH('Table'[Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)
return if(month(EOMONTH(today(),-1))=monthnumber,TRUE())

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Anonymous 

    is this what you want?

    Column = 
VAR monthnumber=SWITCH('Table'[Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)
return if(month(EOMONTH(today(),-1))=monthnumber,TRUE())

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      HI Ryan,

       

      Thank you for this, it has worked, is it possible to do the same with the quarter. so to dynamically get the previous quarter in the same way?

       

      Thanks

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        Anonymous 

        is this the logic that you want for quarter?

         

         

        if it is, you can try this

        Quarter = 
VAR quarternumber=SWITCH('Table'[Month],"January",1,"February",1,"March",1,"April",2,"May",2,"June",2,"July",3,"August",3,"September",3,"October",4,"November",4,"December",4)
VAR currentquarter=CEILING(month(today()),3)/3
return if(currentquarter=1,if(quarternumber=4,"Previous Month",quarternumber&""),if(currentquarter=2,if(quarternumber=1,"Previous Month",quarternumber-1&""),if(currentquarter=3,if(quarternumber=2,"previous Month",if(quarternumber=1,quarternumber&"",quarternumber-1&"")),if(currentquarter=4,if(quarternumber=3,"Prevous month",if(quarternumber=4,quarternumber-1&"",quarternumber&""))))))
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You can have logic like

     

     

    Month Type = Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),true(),
    False()
    )

     

    or

     

    Month Type = Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
    eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
    Format([Date],"MMM-YYYY")
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the reply, the above response worked for me, would you know to to achieve the same but for the quarter. so to always get the previous quarter but dynamically?

       

      Thanks