Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Year Agnostic YTD calculation in Power Query

Hi,

 

I have a Date Table in which i am trying to add a custom column for calculating `IsYTD` which is Year agnostic. To give an example, suppose today's date is 25th Jun 2021. The values in the column should show TRUE for every year X for current year as well as for every year in the past, starting from 1st Jan X till 25th Jun X. Otherwise, for the remaining dates after 25th Jun every year, it should show FALSE.

 

Is this possible to calculate and add this column using Power Query?

Need Help
Show and Tell
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You can add a custom column with a formula like this to that in the query editor.

     

    = let todayday = Date.DayOfYear(Date.From(DateTime.LocalNow()))
    in if Date.DayOfYear([Column1])<=todayday then "YTD" else "Not YTD"

     

    Pat

     

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You can add a custom column with a formula like this to that in the query editor.

     

    = let todayday = Date.DayOfYear(Date.From(DateTime.LocalNow()))
    in if Date.DayOfYear([Column1])<=todayday then "YTD" else "Not YTD"

     

    Pat