Solved: Custom date in power query

RafaelAri · ‎07-09-2024

Hello,

I need custome column in PQ, where I have already "Date" column.

If month is 2 (Feb.) to 11 (Nov.), result is the 1st day of previous month, same year, .

If monthe is 1 (Jan.), result is the 1st day, of month 12 (Dec.), last year.

Please help with the correct "if" query

manvishah17 · ‎07-09-2024

Hi @RafaelAri ,
You can add a custom column and try this ,

if Date.Month([Date]) = 1 then #date(Date.Year([Date])-1, 12, 1) else #date(Date.Year([Date]), Date.Month([Date])-1, 1))

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

Joe_Barry · ‎07-10-2024

Hi @RafaelAri

You can try

Date.From(Date.StartOfMonth(Date.AddMonths([Date], -1)))

Hope this helps

Joe

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

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more

Let's connect on LinkedIn

manvishah17 · ‎07-09-2024

Hi @RafaelAri ,
You can add a custom column and try this ,

if Date.Month([Date]) = 1 then #date(Date.Year([Date])-1, 12, 1) else #date(Date.Year([Date]), Date.Month([Date])-1, 1))

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!