Calculating a Total Based on Start/End Dates II

[kressb]

Hello,

I need a formula that takes into account Start Date and End Date in relation to a "Selected Date" and gives a Total Sum.

I have a data set that includes the Start/End Dates, Site, Object, and ObjectAttributeX (a number equal to or less than 1).

I want to "Selected Date" to come from a Slicer for a Date Dimension.

From the Start Date, the ObjectAttributeX counts for every month thereafter until the End Date.

If an Object's AttributeX changes, an End Date is added and a new line with a new Start Date and the new AttributeX is added.

Ex - raw data:

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

For instance, if I selected from a Slicer:

July 2019 Total ObjectAttributeX = 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
5	7/1/2019	3/31/2020	5	D	1.00
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 Total Object AttributeX = 4.10

Line#	Start Date	End Date	Site	Object	ObjectAttributeX
1	7/1/2019		5	A	0.90
2	7/1/2019		5	B	1.00
4	3/1/2020		5	C	0.20
5	7/1/2019	3/31/2020	5	D	1.00
7	7/1/2019		5	E	0.75
9	7/1/2019		5	G	0.25

April 2020 Total Object AttributeX = 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
4	3/1/2020		5	C	0.20
6	4/1/2020		5	D	0.80
7	7/1/2019		5	E	0.75
9	7/1/2019		5	G	0.25

So when March 2020 is selected in the Slicer, I need the formula to use Line #5 for Object D. When April 2020 is selected in the Slicer, I need the formula to use Line #6 for Object D.

I have a DateDimension, Site Key, and another Table of info already created, this is the relationships/directions for those:

[Icey · Accepted solution]

Hi @kressb ,

No relationship between your Date Dimension table and your Fact table.

Then create measures like so:

Measure = 
VAR SelectedYear =
    SELECTEDVALUE ( 'Date Dimension Table'[Year] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Date Dimension Table'[Month] )
VAR LastDayofSelectedYearMonth =
    EOMONTH ( DATE ( SelectedYear, SelectedMonth, 1 ), 0 )
RETURN
    SWITCH (
        TRUE (),
        SelectedYear = BLANK (), 1,
        MAX ( 'Fact Table'[Start Date] ) <= LastDayofSelectedYearMonth
            && MAX ( 'Fact Table'[End Date] ) = BLANK (), 1,
        MAX ( 'Fact Table'[Start Date] ) <= LastDayofSelectedYearMonth
            && MAX ( 'Fact Table'[End Date] ) > LastDayofSelectedYearMonth, 1
    )

Total = 
CALCULATE (
    SUM ( 'Fact Table'[ObjectAttributeX] ),
    FILTER ( 'Fact Table', [Measure] = 1 )
)

Then, you can put [Measure] as "Filters on this visual" and set "is 1".

BTW, .pbix file attached.

Best Regards,

Icey

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