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
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        OK, spewing numbers at me <> helping.

         

        What is the logic going on here? Why are those numbers included with March 2020 versus April 2020, don't make me try to figure it out, just tell me. The only one that makes sense is July 2019, there are actually 7 rows wtih July 1 2019 as a Start Date. March, I have no idea why there are six March numbers to sum up. There is only one row that matches March 2020. Same for April. So why the magical 6 numbers? 

  • 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.