Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate DateDiff when some records have one date

Hello all, I need a little help with this DAX, it works great as long as there are at least 2 dates, I tried substituting Today() like this IF(ISBLANK ( 'View Name'[Date Previous] ),Today() but it returns 44,791 not the number of days between 7/27 and Today

Date Previous =
CALCULATE (MAX ( 'View Name'[Date] ),
ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
'View Name'[Date] < EARLIER ( 'View Name'[Date] ))

Then I use

Date Diff in Days =
IF (ISBLANK ( 'View Name'[Date Previous] ),
1,DATEDIFF ('View Name'[Date Previous],
'View Name'[Date],
DAY))

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    This seems to work but is there a way to work this out in the Date Previous column?
     
    DateDiffDays =
     If(ISBLANK(Table[aDatePrevious]),DATEDIFF(
            Table[Created],TODAY(),DAY),
    DATEDIFF(Table[aDatePrevious],Table[Created], DAY ) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The above works but it's not really what I am after, I am trying to capture the time each task (UID) spends in each Bucket (BucketID). The DAX is capturing the first change, 7/27 to 8/9 but it's not capturing 8/9 to Today, instead it shows 7/27 to Today. thanks for any direction. I do have a date table joined on Created Date. The data below is in a table, it just looks like there are no columns, copy/paste it into Excel.

      TaskChkListUIDTaskIDBucketIDCreatedaDatePreviousaDateDiffDays
      574164o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnjETjBvMEO0Oy4VTXHq5PC4IAEelR7/27/2022 0:00 22
      836164o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnjETjBvMEO0Oy4VTXHq5PC4IAEelR7/27/2022 0:00 22
      900514o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnjETjBvMEO0Oy4VTXHq5PC4IAEelR7/27/2022 0:00 22
      906474o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnjETjBvMEO0Oy4VTXHq5PC4IAEelR7/27/2022 0:00 22
      943404o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnjETjBvMEO0Oy4VTXHq5PC4IAEelR7/27/2022 0:00 22
      299704o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnFZQB41MS6U256wmEXIrYroIAGlSY8/9/2022 0:007/27/2022 0:0013
      346194o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnFZQB41MS6U256wmEXIrYroIAGlSY8/9/2022 0:007/27/2022 0:0013
      443094o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnFZQB41MS6U256wmEXIrYroIAGlSY8/9/2022 0:007/27/2022 0:0013
      468154o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnFZQB41MS6U256wmEXIrYroIAGlSY8/9/2022 0:007/27/2022 0:0013
      574164o_xrb0IOU29UHkQ408RR4IAF5En4o_xrb0IOU29UHkQ408RR4IAF5EnFZQB41MS6U256wmEXIrYroIAGlSY8/9/2022 0:007/27/2022 0:0013
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Sorry this is what I am using, I coppied it from MS Azure DevOps reporting page and adapted it to my model. Like I mentioned the DAX is capturing the first change/Days in Bucket 7/27 to 8/9 which is great, however I need the days in the new Bucket 8/9 to Today(). Just to mention, currently most of the records only have one date 7/27, no changes (this will change as records are updated) and the calculation is correct 7/27 to Today(), unfortunately this calculation is also applied to the records with change. Thanks for any help

         

         

        State Time in Days =
        CALCULATE (
        SUM ( 'View Name'[Date Diff in Days] ),
        ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
        'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
        'View Name'[State] = EARLIER ( 'View Name'[State] )
        ) + 0

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Can you give me a sample data or PBIX file without private information ? So that can be more convenient for me to do testing.

    Thank you in advance.

     

    Best regards,

    Community Support Team Selina zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello thank you for the response, there is sample data below. I can provide the data set, 423 records but I don't see how to attach a file, do you have a location I can upload to?