Forum Discussion

RafaelAri's avatar
RafaelAri
Helper III
2 years ago
Solved

Custom date in power query

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

 

Need Help
  • manvishah17's avatar
    manvishah17
    2 years ago

    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!

     

     

3 Replies

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    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!

     

     

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    Hi RafaelAri 

     

    You can try

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

     

    Hope this helps

    Joe