cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tejapowerbi123
Helper IV
Helper IV

Power Query "if condition Logic Help"

Hello Community,

Can you please review below and provide your suggestions? I am looking some suggestions using Power Query.

 

Date(mm/dd/yyyy)

12/22/2020

12/23/2020

12/24/2020

1/2/2021

1/3/2021



Logic:1
If Date.Month(DateTime.LocalNow())=1 then it should display data from previous month and previous year

Date(mm/dd/yyyy)

12/22/2020

12/23/2020

12/24/2020


Logic:2
if Date.Month(DateTime.LocalNow())<>1 then it should display only previous month data and same year.

Date(mm/dd/yyyy)

1/2/2021

1/3/2021

 

if
Date.Month(DateTime.LocalNow())=1

then Date.Month ([#"Date(mm/dd/yyyy)"])-1, Date.Year(DateTime.LocalNow())-1

else

Date.Month(DateTime.LocalNow()),Date.Year(DateTime.LocalNow())

Let me know if you need more information.

 

 

2 ACCEPTED SOLUTIONS
mahoneypat
Super User IV
Super User IV

Looks like you just want the filter to be for the previous full month.  To do that, just click on the arrow on your date column and make a hard-coded set of date by choosing "Between".  Once that is created, update it with the red text changes below

 

= Table.SelectRows(#"Previous Step", each let today = Date.From(DateTime.LocalNow()) in [Date] >= Date.StartOfMonth(Date.AddMonths(today,-1)) and [Date] <= Date.EndOfMonth(Date.AddMonths(today,-1)))

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

parry2k
Super User III
Super User III

@tejapowerbi123 solution attached, check steps in transform data. I calculated the start and end date and then used it for the filter. you can tweak the logic as per your need.

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
parry2k
Super User III
Super User III

@tejapowerbi123 solution attached, check steps in transform data. I calculated the start and end date and then used it for the filter. you can tweak the logic as per your need.

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

mahoneypat
Super User IV
Super User IV

Looks like you just want the filter to be for the previous full month.  To do that, just click on the arrow on your date column and make a hard-coded set of date by choosing "Between".  Once that is created, update it with the red text changes below

 

= Table.SelectRows(#"Previous Step", each let today = Date.From(DateTime.LocalNow()) in [Date] >= Date.StartOfMonth(Date.AddMonths(today,-1)) and [Date] <= Date.EndOfMonth(Date.AddMonths(today,-1)))

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hi @mahoneypat  and @parry2k ,

Thank you so much for great suggestions.Both solutions are working for me.

Thanks Again.

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!

Top Solution Authors
Top Kudoed Authors