Running Total using tables not in relationship

[Mike2024]

Measure with data type number: Paid USD - Running Total =

var LastAdate = CALCULATE(max(movement_view[Development Advised Date]),all(movement_view))

 return

 if(SELECTEDVALUE(Development[Development Month])> LastAdate,BLANK(),

    CALCULATE([Paid],

    FILTER(ALLSELECTED(Development),

        Development[Development Month]<=LastAdate

)))

Column with data type number: movement_view[Development Advised Date] =

((YEAR(movement_view[Advised Date]) - movement_view[YOA]) * 12) + MONTH(movement_view[Advised Date])  

Measure with data type number:Paid=

    CALCULATE(  

        SUM(movement_view[PaidTotal]),  

        FILTER(  

            movement_view,  

            movement_view[Advised Date] <= [MaxDate]

            && movement_view[Advised Date] >= [MinDate]

        )  

    )

Measure with data type date: MaxDate = Max('calendar'[Date])

Measure with data type date: MinDate = Min('calendar'[Date])

Tables calendar, movement_view and Development are not connected via any relationship and should not be connected via a relationship as I would like to use calender to date and Development as an unconnected dimension tables.

Paid is calculating the [PaidTotal] but it is not showing as a running total and it's displaying a total flat line to the max Development[Development Month] in the whole table despite it being lower than the max movement_view[Development Advised Date] when 'calendar[Date] slicer is used.

I want Paid to calculate the running total for [PaidTotal] up to the max movement_view[Development Advised Date] when 'calendar'[Date] slicer is used

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

[Mike2024]

'calendar' table is a table, with 1 column, that list dates between 2009 to today

Development table is a table, with 1 column, development=

GENERATESERIES(1,DATEDIFF(min('calendar'[Date]),max('calendar'[Date]), Month))

movement_view table is as below

Advised Date [DD/MM/YYYY]	YOA	Paid	Development Advised Date
01/01/2009	2009	10	1
05/03/2009	2009	5	3
09/05/2009	2009	20	5
01/01/2010	2010	15	1
02/02/2010	2010	10	2
05/02/2010	2010	5	2
01/01/2010	2009	20	13
06/02/2010	2009	25	14
05/05/2010	2010	30	5

My expected outcome, is that when i put development on a line graph visual x-axis, YOA as the legend, Running total paid as the value, i would get a running total of the paid, per each YOA, up to the max Development Advised Date. In this case, YOA 2009 would have a line up to 14 while 2010 would have a line up to 5 on the x-axis. when i use 'calendar[Date] as a slicer, for example max value 31/01/2010, the YOA 2009 would have a line up to 13 while 2010 would have a line up to 1 on the x-axis

[lbendlin]

You would need to wire your Advised date into the calendar table if you want to filter the calendar.

[Mike2024]

Thanks for your response. Is it not possible to have the running total based on the max and min of the calendar date? I was hoping to keep the calendar table an unconnected dimension table, not linked via a relationship. Also the x-axis should be the development periods

[lbendlin]

not sure how that would look like.  Can you share a mockup?