Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating a Total Based on Start/End Dates II

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

 

For instance, if I selected from a Slicer:

July 2019 Total ObjectAttributeX = 5.25

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

 

March 2020 Total Object AttributeX = 4.10

Line#Start DateEnd DateSiteObjectObjectAttributeX
17/1/2019 5A0.90
27/1/2019 5B1.00
43/1/2020 5C0.20
57/1/20193/31/20205D1.00
77/1/2019 5E0.75
97/1/2019 5G0.25

 

April 2020 Total Object AttributeX = 3.90

Line#Start DateEnd DateSiteObjectObjectAttributeX
17/1/2019 5A0.90
27/1/2019 5B1.00
43/1/2020 5C0.20
64/1/2020 5D0.80
77/1/2019 5E0.75
97/1/2019 5G0.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:

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    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.

1 Reply

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    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.