Convert hardcoded month to dynamic month, based on current month

[SJHALANI]

Hi,

I have written the below query:

= Table.AddColumn(#"Removed Duplicates1", "Last Month of Funds", each if [PO Projections] = "No Funds" then "No Funds" else if [PO Projections] = "OK" then "Sufficent for 3 service months" else if [PO Projections] = false then false else if [PO Projections] = "One cycle budget" then "Apr'23" else if [PO Projections] = "Two cycles budget" then "May'23" else if [PO Projections] = "Three cycles budget" then "Jun'23" else [PO Projections])

Right now the months in my "Last Month of Funds" table are static. I want those to be dyamic so that when PO Projections = "One cycle budget", then Last Month of Funds should be based on the previous month. For example, when the actual month changes to June, the Last Month of Funds should say "May'23" in this case.

Similarly, when PO Projections = "Two cycles budget", Last month of Funds should be the current month.

And when when PO Projections = "Three cycles budget", Last month of Funds should be the next month.

So, question being, what changes should I make to my existing code to make it dynamic.

Any help would be appreciated! Thanks!

[v-cgao-msft · Accepted solution]

Hi @SJHALANI ,

You can replace them with these codes.

current month(May 23):

DateTime.ToText(DateTime.LocalNow(),[Format="MMM yy"])

 last month(Apr 23):

DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),-1),[Format="MMM yy"])

next month(Jun 23):

DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),1),[Format="MMM yy"])

Best Regards,
Gao
Community Support Team

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!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum