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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors