Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculating a Total Based on Start/End Dates

I have a data set that includes Start/End Dates, Site, Object, and ObjectAttributeX (a number equal to or less than 1). I need to calculate Total ObjectAttributeX for a Site. If an Object's Attribu...
Need Help
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Greg_DecklerI'm wondering if I confused the issue by calling the column I'm trying to sum "time"?

    I've editted my post above.

    My issue is when I sum March 2020 I need a formula that uses (for example) the first line for Object D (1.00) and when I sum April 2020 I need it to use the second line for Object D (0.80). I don't think time/duration plays into it?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Now I'm confused, I done understand how you are getting from the sample source data to your desired results.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Greg_Deckler 
        July 2019: N=7, Total ObjectXAttribute=5.25

          7 Objects: A, B, C, D, E, F, G

          Sum of ObjectXAttribute = 0.90 + 1.00 + 0.50 + 1.00 + 0.75 + 0.85 + 0.25 = 5.25

        Line#Start DateEnd DateSiteObjectObjectAttributeX
        17/1/2019 5A0.90
        2 7/1/2019 5B1.00
        3 7/1/20192/29/20205C0.50
        43/1/2020 5C0.20
        5 7/1/20193/31/20205D1.00
        64/1/2020 5D0.80
        7 7/1/2019 5E0.75
        8 7/1/201912/31/20195F0.85
        9 7/1/2019 5G0.25

         

        March 2020: N=6,Total ObjectXAttribute=4.10

        6 Objects: A, B, C, D, E, G

        Sum of ObjectXAttribute = 0.90 + 1.00 + 0.20 + 1.00 + 0.75 + 0.25 = 4.10

        Line#Start DateEnd DatwSiteObjectObjectAttributeX
        17/1/2019 5A0.90
        27/1/2019 5B1.00
        37/1/20192/29/20205C0.50
        43/1/2020 5C0.20
        57/1/20193/31/20205D1.00
        64/1/2020 5D0.80
        77/1/2019 5E0.75
        87/1/201912/31/20195F0.85
        97/1/2019 5G0.25


        April 2020: N=6, Total ObjectXAttribute=3.90

        6 Objects: A, B, C, D, E, G

        Sum of ObjectXAttribute = 0.90 + 1.00 + 0.20 + 0.80 + 0.75 + 0.25 = 3.90

        Line#Start DateEnd DateSiteObjectObjectAttributeX 
        17/1/2019 5A0.90
        27/1/2019 5B1.00
        37/1/20192/29/20205C0.50
        43/1/2020 5C0.20
        57/1/20193/31/20205D1.00
        64/1/2020 5D0.80
        77/1/2019 5E0.75
        87/1/201912/31/20195F0.85
        97/1/2019 5G0.25
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      If this is all you need I believe you can create a measure using the SUMX function: https://docs.microsoft.com/en-us/dax/sumx-function-dax

       

      I'm not sure why you are using the End date for some sums and the start date for others. This is the part that needs clarification.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Greg_Deckler I may be incorrect and I would defer to your judgment for sure on that, but I'm going to ask for clarification for myself because unless I am mistaken the link you posted converts the days, hours, and seconds to numbers and then sticks them in a custom format to generate the appearance of duration. I know duration is supported in Power Query (M): https://docs.microsoft.com/en-us/powerquery-m/duration-functions, but it seems your article follows a similar process to the link I posted.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous  - Both Power Query and Power BI's representation of Dates, Time and Duration is a trick. It's all just decimal numbers. You can provie this to yourself in Power Query if you right-click a "Duration" column and switch it to decimal number. You get a decimal number. Switch it back, it is magically a duration. Dates and Times in DAX are the same trick, it's just a decimal number behind the scenes.

       

      So, converting somethingt that is formatted as a duration to a number, it's just a matter of whether it happens automatically or not. The problem that was fixed by Chelsie Eiden is that Power BI had no capability to display an aggregation as a duration format. So, even if you converted something to a number, added it up, etc. you had to convert it back to text to display it as a duration. Chelsie Eiden fixed that in Power BI so that you can have a number but display it as a duration, just like you can have a number and display it as a date.

       

      I could actually argue this both ways. One, Power BI always supported duration because duration is just a decimal number. Conversely, Power BI never supported duration until Chelsie Eiden because you could never display an actual duration format, it always had to be converted to text. Either way, in my opinion, Power BI now supports Durations post Chelsie Eiden's work whereas previously it did not. I think you might be getting hung up on the fact that "duration" is not a "native data type". Well, it isn't in Power Query either because internally it is just a decimal number. Is duration in Power BI Desktop as nice and friendly as in Power Query? No, but it is definitely good enough to check the box of being supported.