cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RBraun
Helper II
Helper II

If then query with dates

I am trying to write an if then statement in power query for a custom column named "2023 Days".  The purpose is to give me the total days a project is active in 2023.

 

I have the following data:

Actual Start DatePlanned Completion Date
12/1/20222/1/2024
11/1/20226/1/2023
10/1/20227/1/2023
9/1/20228/1/2023

 

The logic I would like to apply is this: if Planned Completion Date is greater than 12/31/2023, then 2023 Days = 365, otherwise 2023 Days = (Planned Completion Date - 1/1/2023).

 

Thank you in advance for your help and please let me know if there's anything else I can provide

1 ACCEPTED SOLUTION
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @RBraun 

 

if [Planned Completion Date] > #date(2023,12,31) then 365 else [Planned Completion Date] - #date(2023,1,1)

 

Use formula

Mikelytics_0-1671547994262.png

transform to whole number

Mikelytics_1-1671548022734.png

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

1 REPLY 1
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @RBraun 

 

if [Planned Completion Date] > #date(2023,12,31) then 365 else [Planned Completion Date] - #date(2023,1,1)

 

Use formula

Mikelytics_0-1671547994262.png

transform to whole number

Mikelytics_1-1671548022734.png

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors