Calculating a Total Based on Start/End Dates

[kressb]

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 AttributeX changes, an end date is added and there is a new line with a new start date and the new AttributeX.

If the Object stops being used, an end date is put in, ex:

Line#	Start Date	End Date	Site	Object	ObjectAttributeX
1	7/1/2019		5	A	0.90
2	7/1/2019		5	B	1.00
3	7/1/2019	2/29/2020	5	C	0.50
4	3/1/2020		5	C	0.20
5	7/1/2019	3/31/2020	5	D	1.00
6	4/1/2020		5	D	0.80
7	7/1/2019		5	E	0.75
8	7/1/2019	12/31/2019	5	F	0.85
9	7/1/2019		5	G	0.25

I want PowerBI to give me a summary like this (preferably with the Date as a Slicer filter):

Site 5	N	Total ObjectAttributeX
July 2019	7	5.25
March 2020	6	4.10
April 2020	6	3.90

TYIA

ETA: this has nothing to do with time. 

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). hope this helps clarify my issue.

[Greg_Deckler]

Chelsie Eiden's Duration: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

The other reply is technically incorrect, Power BI does support duration formats now.

[Tad17]

@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]

@Tad17  - 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.

[kressb]

@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]

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

[kressb]

@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 Date	End Date	Site	Object	ObjectAttributeX
1	7/1/2019		5	A	0.90
2	7/1/2019		5	B	1.00
3	7/1/2019	2/29/2020	5	C	0.50
4	3/1/2020		5	C	0.20
5	7/1/2019	3/31/2020	5	D	1.00
6	4/1/2020		5	D	0.80
7	7/1/2019		5	E	0.75
8	7/1/2019	12/31/2019	5	F	0.85
9	7/1/2019		5	G	0.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 Date	End Datw	Site	Object	ObjectAttributeX
1	7/1/2019		5	A	0.90
2	7/1/2019		5	B	1.00
3	7/1/2019	2/29/2020	5	C	0.50
4	3/1/2020		5	C	0.20
5	7/1/2019	3/31/2020	5	D	1.00
6	4/1/2020		5	D	0.80
7	7/1/2019		5	E	0.75
8	7/1/2019	12/31/2019	5	F	0.85
9	7/1/2019		5	G	0.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 Date	End Date	Site	Object	ObjectAttributeX
1	7/1/2019		5	A	0.90
2	7/1/2019		5	B	1.00
3	7/1/2019	2/29/2020	5	C	0.50
4	3/1/2020		5	C	0.20
5	7/1/2019	3/31/2020	5	D	1.00
6	4/1/2020		5	D	0.80
7	7/1/2019		5	E	0.75
8	7/1/2019	12/31/2019	5	F	0.85
9	7/1/2019		5	G	0.25

[Greg_Deckler]

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? 

[kressb]

Once an object starts, the ObjectAttributeX counts for every month thereafter until the end date.
For March, all the lines that started 7/1/2019 count towards the March total, except for lines 3 and 8 because they have end dates prior to 3/1/20.

[Greg_Deckler]

OK, should be something along the lines of: 

Column = 
    VAR __Table = FILTER(ALL('Table'),[Start Date]<=[Date] && ('Table'[End Date]>=[Date] || ISBLANK('Table'[End Date])))
RETURN
    SUMX(__Table,[ObjectAttributeX])

Attached PBIX file

[Tad17]

@Greg_Deckler 

Makes sense. I understand how my statement saying Power BI does not support duration is misleading. It certainly does it just has do be in decimal format and then converted to duration in the article you posted. This is essentially what the post I included states as well though I understand saying duration is not supported is technically incorrect. Thanks for the learning moment 🙂

@kressb 

I think your answer may be found in this thread: https://community.powerbi.com/t5/Desktop/Count-of-Open-or-Closed-items-based-on-2-different-Date-Col...

[Greg_Deckler]

Thank-you. I will take a closer look. But, dude, how did you expect me to figure that out?

[kressb]

@Greg_Deckler I'm sorry! very new to this, not sure how to explain accurately.

So I want the date that the data is filtered to be based off a datedimension table that i built. I put the DateDimension into a Slicer to filter the data in the "Report" tab.

I have 4 tables right now - a site key, the objectattributex by date, objectattributex need, and a date dimension:

Is it possible to make this work?

[Greg_Deckler]

Maybe. What are the directions and of those relationships? And are they 1:many, 1:1, many:1, many:many? Is the ObjectAttributeX Need is that what we are trying to create??

[kressb]

@Greg_Deckler 

Ok, I drew them but I can write it out if that's easier?

ObjectXNeed is something I already have.

I'm trying to create how much I have (ObjectAttributeX) on any given date, based on the information in this table:

[Tad17]

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.

[Tad17]

Hey @kressb 

Power BI does not support Duration formats or calculations so you will need to convert your dates to numbers of either days, minutes, etc. See the below links for more on this:

https://community.powerbi.com/t5/Desktop/Duration-format/td-p/447543

If this helps please kudo.

If this solves your problem please accept it as a solution.