Forum Discussion

renlaforest's avatar
renlaforest
Helper I
4 years ago
Solved

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...
target
to date
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, renlaforest 

     

    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.

     

    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.

     

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
4 years ago

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

  • renlaforest's avatar
    renlaforest
    Helper I
    4 years ago

    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:

     

     

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Community Support
      4 years ago

      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.

      • renlaforest's avatar
        renlaforest
        Helper I
        4 years ago

        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
        )