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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors