topic Re: Calculate cumulative daily amount based selected dates in DAX Commands and Tips

Calculate cumulative daily amount based selected dates

JB_AT — Wed, 28 Feb 2024 13:39:59 GMT

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

Re: Calculate cumulative daily amount based selected dates

lbendlin — Thu, 29 Feb 2024 00:04:58 GMT

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

Re: Calculate cumulative daily amount based selected dates

JB_AT — Thu, 29 Feb 2024 06:36:35 GMT

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

Re: Calculate cumulative daily amount based selected dates

Anonymous — Thu, 29 Feb 2024 07:48:36 GMT

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.

Re: Calculate cumulative daily amount based selected dates

lbendlin — Wed, 06 Mar 2024 00:20:59 GMT

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-Forum/ba-p/963216

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