Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous 

 

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 @Anonymous 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.