Forum Discussion

MaJar's avatar
MaJar
Frequent Visitor
1 year ago
Solved

Create an additive measure on a Date/Time column

Hello Experts,

 

I have 4 columns in my dataset that looks like the below:

IDCreated_DateTotal_Duration_minutesStatus
101/01/2025 07:00:00120Open
101/01/2025 07:00:0060Validated
101/01/2025 07:00:0030In progress
212/28/2024 10:30:0010Open
212/28/2024 10:30:0060Validated

 

I would like to create a measure that will provide the below output:

IDCreated_DateTotal_DurationDate_After_Addition
101/01/2025 07:00:0021001/01/2025 10:30:00
212/28/2024 10:30:007012/28/2024 11:40:00

 

My measure should cumulate the values in the Total_Duration_minutes column and provide the final date/time after adding the sum of the total_duration_minutes to the created_date column for each ID. Please also note that my measure should respect the filtering context of the Status column. So, in case I filter out the value "Validated", the output for ID(1) should look like the below:

IDCreated_DateTotal_DurationDate_After_Addition
101/01/2025 07:00:0015001/01/2025 09:30:00

 

If the solution requires the creation of a calculated table, that's fine too!

 

Thank you in advance!

Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    1 year ago

    Hy MaJar 

    You can also get your desire output by using these 2 measures in your report.

     

    Total_Duration = SUM('Table'[Total_Duration_minutes])
    Date_After_Addition = MIN('Table'[Created_Date]) + ([Total_Duration] / 1440)

     

    Best Regards,
    Muhammad Yousaf

     

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

     

    LinkedIn

  • Fowmy's avatar
    Fowmy
    1 year ago

    MaJar 

    If you add the status in your visual, it will be part of the grouping, use the first DAX formula if you need to keep only one line per ID

6 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    1 year ago

    MaJar 

    Measure for Total Duration:

    Total Duration = SUM(Table1[Total_Duration_minutes])


    Measure for Date_After_Addition:

    Date_After_Addition = MAXX( VALUES(Table1[Created_Date]), Table1[Created_Date]) + TIME(0,[Total Duration],0)



     




    • MaJar's avatar
      MaJar
      Frequent Visitor
      1 year ago

      Hi,

       

      Unfortunately, with this option, I loose the option to filter out the time spent on some status and the final time added will always be the same when I filter out some values.

       

      Thank you!

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        1 year ago

        MaJar 

        If you add the status in your visual, it will be part of the grouping, use the first DAX formula if you need to keep only one line per ID

  • MaJar's avatar
    MaJar
    Frequent Visitor
    1 year ago

    Hello Fowmy,

     

    Thank you very much for your input.

     

    I still need to have the ID and Status columns in my table. Once I add them into the table following your logic, the grouping doens't work and the measure are treating every row separately and not grouped by ID.

    Can you advice what should I do to have the output table as I published in my post.

     

    Thank you in advance

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      1 year ago

      MaJar 

      This should work:

      Date_After_Addition = 
CALCULATE(
    MAXX( VALUES(Table1[Created_Date]), Table1[Created_Date]) + TIME(0,[Total Duration],0),
    ALLEXCEPT( Table1 , Table1[Created_Date] )
)
  • muhammad_786_1's avatar
    muhammad_786_1
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hy MaJar 

    You can also get your desire output by using these 2 measures in your report.

     

    Total_Duration = SUM('Table'[Total_Duration_minutes])
    Date_After_Addition = MIN('Table'[Created_Date]) + ([Total_Duration] / 1440)

     

    Best Regards,
    Muhammad Yousaf

     

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

     

    LinkedIn