Calculate cumulative daily amount based selected dates

[JB_AT]

Helllo

I have a basic table Table1

From	To	DailyAmount
02/01/2024	05/2024	50

From has an active one to many relationship with the 'Date Table'[Date] and To has an Inactive

I want to create a measure that calculates a cumulative amount based on the selected dates in the Date Slicer.

The Cumulative amount should stop when the To date has been passed. 

Eaxmple I choose 01/01/2024 - 10/01/2024, then the cumlative amount should be only 200

How can I achiev this?

I have this measure below which calculates correctly based on the selected dates

VAR SelectedStartDate = MIN('Date Table'[Date])
VAR SelectedEndDate = MAX('Date Table'[Date])
RETURN
CALCULATE(
    SUMX(
        FILTER(
            ALL('Date Table'),
            'Date Table'[Date] >= SelectedStartDate && 'Date Table'[Date] <= SelectedEndDate
        ),
        [DailyAmount] * DATEDIFF(SelectedStartDate, SelectedEndDate, DAY)
    ),
    'Date Table'[Date] <= MAX('Date Table'[Date])
)

Thanks

[Anonymous]

Hi @JB_AT ,

I don't think the one-to-many relationship of 'From' can help filter the data in 'Date Table'. It is recommended that you create a continuous date column using the row values of the 'From' column and the row values of the 'To' column.
Use the following DAX expression to create a table :

CALENDAR(DATE([From].year,[From].MonthNo,[From].day),DATE([To].year,[To].MonthNo,[To].day))

Create a one-to-many relationship between the created table and the 'Date Table'. Add the date column of the created table to the slicer.

[lbendlin]

From has an active one to many relationship with the 'Date Table'[Date] and To has an Inactive

Make the From relationship inactive as well (or remove both).  Relationships do not help in your scenario.  

Use measures to overlap the Date range from the slicer with the date range From-To.  You can use (roughly)

INTERSECT(VALUES('Date Table'[Date]),CALENDAR([From],[To]))

[JB_AT]

@lbendlin Thank you for your reply

I have tried implementing this into my scenario and looked at documentation and videos on Intersect, but I can't get it.

If you have time, how would you build it in to my measure above?

Kind regards

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...