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
renlaforest
Helper I
Helper I

Target to date

Hello - I'm new to Power BI and DAX. I've strugged with figuring out the following.

I have a start date for the project and a close date, and I created a column to calculate the total number of days. 

renlaforest_0-1650896879646.png

I've also got a total target column for those dates. If the total target is 1000, and yesterday was half way through the start and close dates, I'd like the target to be 500. If today's after the close date, I'd like the target to be 1000. For clarify, if today's the close date, and the project is open for 10 days, I'd like the target to be 900.

renlaforest_1-1650897095595.png

In the example above, the target shouldn't be 3288 - it should be 1644 since we're half way through the dates that the project is open.

Thank you in advance for your help!

1 ACCEPTED SOLUTION

Hi, @renlaforest 

 

vjaneygmsft_0-1651562814985.png

This error shows that you are using the custom column feature in powerquery.

But the code is DAX, not M code. You need to create it in desktop.

vjaneygmsft_1-1651563004035.png

vjaneygmsft_2-1651563087382.png

 

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

7 REPLIES 7
renlaforest
Helper I
Helper I

@v-janeyg-msft - the table has dates from 2021 to 2023. The snip that I took is from the top, which only shows the older, closed projects. There are dates in the table that span each week of the year. Hope that helps.

v-janeyg-msft
Community Support
Community Support

Hi, @renlaforest 

 

I don't understand your requirements. Your dates are all in 2021, and the examples you gave are all today and yesterday. Is there any connection between them? Can you explain more details?

 

Best Regards,
Community Support Team _ Janey

@v-janeyg-msft - the table has all of the dates for our projects between 2021 and 2023, and you're seeing the older projects at the top of the list. If I wanted to do this in Excel, there may be a more elegant way to do this, but I'd use a formula like this one to do what I'm trying to do here:

 

renlaforest_0-1651164499546.png

 

Hi, @renlaforest 

 

You can try:

column =
IF (
    TODAY () > [Start],
    IF (
        TODAY () <= [Close],
        [Total target]
            / ( DATEDIFF ( [Start], [Close], DAY ) + 1 )
            * ( DATEDIFF ( [Start], TODAY (), DAY ) + 1 ),
        [Total target]
    ),
    0
)

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much for your help. For some reason, and I've tried Googling (unsuccesfuly) what could be causing it, but I get an error message: Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.

I have:

IF (
TODAY () > [Start Date],
IF (
TODAY () <= [Close Date],
([EN Targeted Completes]
/ ( DATEDIFF ( [Start Date], [Close Date], DAY ) + 1 ))
* ( DATEDIFF ( [Start Date], TODAY (), DAY ) + 1 ),
[EN Targeted Completes]
),
0
)

Hi, @renlaforest 

 

vjaneygmsft_0-1651562814985.png

This error shows that you are using the custom column feature in powerquery.

But the code is DAX, not M code. You need to create it in desktop.

vjaneygmsft_1-1651563004035.png

vjaneygmsft_2-1651563087382.png

 

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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.

Top Solution Authors