Calculate cumulative total by multiplying value to itself.

[third_hicana]

Hi. Asking for your help on calculating cumulative total by multiplying the average value to itself. I want to achieve the line chart below. My example is Id 1.
For the data model the relationship of date table to Table 1 is inactive because there is another active relationnship in that table. For the sake of simplicity I just shown how End Date is connected to the date table as well as Start date from another table to the date table.

Thank you in advance for your help. 

[sanalytics]

@third_hicana 
Please take this below solution..
Just to add one suggesation.. Always follow below statement

"Data should be transformed as far upstream as possible, and as far downstream as necessary."

This is not my statement.. This is Maxim law and that should follow every data modellar and  BI developer. Just do the simple google search and you will able to understand the significance of this statement.

Now, come to your solution.. Below is the logic
1) First, You need to prepare you data.
2) Second, write a simple cumulative DAX.(NO FANCY DAX)
For preparing data. follow below m code

let
    Source = Table2,
    #"Merged Queries" = Table.NestedJoin(Source, {"ID"}, Table1, {"ID"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Daily_Average", "End Date"}, {"Daily_Average", "End Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Date", each List.Dates([Start Date],
Number.From([End Date] - [Start Date] ) + 1,#duration(1,0,0,0)
)),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
    #"Changed Type"

2) Use below DAX

IF(
 ISBLANK( SUM( 'Table'[Daily_Average] ) ),BLANK(),
CALCULATE(
    SUM( 'Table'[Daily_Average] ),
FILTER(
    ALL( 'Date Table'),
 'Date Table'[Date] <= MAX( 'Date Table'[Date] )
)) )

if you want to show ID wise cumulative then Add VALUES function after the Filter function.

Below screenshot

Attached the pbix file for your refrence.

Hope it helps

Regards

sanalytics

[v-mdharahman]

Hi @third_hicana,

Thanks for reaching out to the Microsoft fabric community forum.

From your model and the chart, the goal is to display a running total where the daily value is calculated by multiplying the "Daily_Average" by the number of days since the Start Date, up until the End Date.

One key thing to note is the inactive relationship between your Date Table and the End Date in Table 1. Since Power BI only allows one active relationship between two tables at a time, it's defaulting to the active Start Date relationship from Table 2. Because of this, your calculation won’t automatically consider the full range from Start Date to End Date for each ID.

To get the cumulative total as shown in your chart (80, 160, 240, etc.), you'll need to create a measure that uses "USERELATIONSHIP()" to activate the connection to End Date when needed. Then filter the dates so that only the ones between Start Date and End Date are included. And then multiply the "Daily_Average" by the number of days since the Start Date (including the current day).

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[third_hicana]

Hi @v-mdharahman 
Could you give me a dax pattern of your solution please? 🙂

I tried this one but failed to see the result I am expecting

VAR CurrentDate = MAX(DateTable[Date])

RETURN
CALCULATE(FILTER(Table2, Table2[StartDate] <= CurrentDate && RELATED(Table1[EndDate] >= CurrentDate), Table1[Daily_Average] * [NumberOfDays]), USERELATIONSHIP(Table1[EndDate], DateTable[Date]))