Solved: Power Query "if condition Logic Help"

tejapowerbi123 · ‎01-04-2021

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.

mahoneypat · ‎01-04-2021

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 · ‎01-04-2021

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

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

parry2k · ‎01-04-2021

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

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.

mahoneypat · ‎01-04-2021

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