cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sher
Helper I
Helper I

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?

 

for power bi forum.PNG

1 ACCEPTED SOLUTION
ryan_mayu
Super User II
Super User II

@Sher 

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

1.PNG





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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User II
Super User II

@Sher 

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

1.PNG





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

Proud to be a Super User!




View solution in original post

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

@Sher 

is this the logic that you want for quarter?

 

1.PNG 

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




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

Proud to be a Super User!




amitchandak
Super User IV
Super User IV

@Sher , 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")
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.