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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors