Forum Discussion
Running Total using tables not in relationship
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
5 Replies
- lbendlin
Super User
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'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 belowAdvised 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.
