Running total

[Raul]

Hello everybody,

I have a CALENDAR table in my data model. I have too two more tables: WAREHOUSES and TARGETS:

ID	WAREHOUSE
A	Warehouse1
B	Warehouse2
C	Warehouse3

YEAR	WAREHOUSE	TARGET
2017	A	$    5.000,00
2017	B	$ 10.000,00
2017	C	$ 20.000,00
2016	A	$    6.000,00
2016	B	$ 25.000,00
2016	C	$ 18.000,00

The TARGETS table is not related with de CALENDAR table but is related with the WAREHOUSES table. I have too a measure MonthTarget = SUM(Target)/12

I need the running total for Target field in this matrix visualization:

YEAR	2017
WAREHOUSE	A

Month	Target
January	$       416,67
February	$       833,34
March	$    1.250,00

The YEAR and WAREHOUSE fields are filters and the value of 416,67 is the MonthTarget value of the A warehouse for the year 2017. For a whole year it would be:

YEAR	2017
WAREHOUSE	A
Month	Target
January	$       416,67
February	$       833,34
March	$    1.250,00
---	$    1.666,67
---	$    2.083,34
---	$    2.500,00
---	$    2.916,67
---	$    3.333,34
---	$    3.750,00
---	$    4.166,67
---	$    4.583,34
December	$    5.000,00

How can I calculate this measure?

Thank you.

[Greg_Deckler]

Have you tried the Running Total quick measure?

[Raul]

Yes, but the result is the same value of the month target for every mont, like this:

Month	Target	RunningTotal
January	$       416,67	$       416,67
February	$       833,34	$       416,67
March	$    1.250,00	$       416,67

[Greg_Deckler]

Can you post that formula?

[Raul]

RunningTotal =
CALCULATE(
[MonthTarget];
FILTER(
ALLSELECTED('Calendar'[Month]);
ISONORAFTER('Calendar'[Month]; MAX('Calendar'[Month]); DESC)
)
)

[Greg_Deckler · Accepted solution]

In looking at your data layout and calculation, I would create a MONTH column in your Calendar table and do something like:

Measure = MonthTarget * AVERAGE(Calendar[Month])

[Raul]

Great!!! It works perfectly.

Thank you very much @Greg_Deckler

[Greg_Deckler]

Happy to help! 🙂