How do you create a running total using a number series rather than a date?

Question

Greetings, PBI Community. I'm needing help with creating a running total using a month sequence number rather than a date. Here's the scenario:

I have a table of data as follows:

AllocDate	StartDate	Sales	Client	Month Sequence
1/1/2021	12/1/2020	100	AcmeTech	1
1/2/2021	12/1/2020	100	AcmeTech	1
2/1/2021	12/1/2020	200	AcmeTech	2
4/1/2021	12/1/2020	300	AcmeTech	4
5/1/2021	12/1/2020	100	AcmeTech	5
1/1/2020	10/1/2019	500	LuxTech	3
2/1/2020	10/1/2019	100	LuxTech	4
3/4/2020	10/1/2019	200	LuxTech	5
5/2/2021	10/1/2019	300	LuxTech	19

This table is connected to a calculated table that has a list of numbers from 1-24 for 2 years' worth of months:

I'm wanting to put the Month Sequence[Month No.] field into a table and have the month number total and the running total shown side by side:

I can write these for dates easily, but that same method doesn't seem to work with non-dates.... Or I'm writing them incorrectly.

aj1973 · Answer

Hi&nbsp;arpost&nbsp;Did you try this?&nbsp;

arpost · Answer

aj1973, thanks for replying! That's super close, but is there a way to configure it so that uses a relationship with a parent table (1 side of a 1:* as in model image above) and shows 0 if there is nothing for a given period?

aj1973 · Answer

Replace it to both&nbsp;

v-henryk-mstf · Answer

Hi&nbsp;arpost&nbsp;,
&nbsp;
Is&nbsp;aj1973&nbsp; suggestion helpful? If the problem has been solved, please mark the correct response as the solution to help the other members find it more quickly. If the problem persists, please point it out.
Best Regards,Henry
&nbsp;

arpost · Answer

Thanks for the reply,&nbsp;aj1973! Unfortunately, changing that relationship to bidirectional didn't do the trick. The only semi-workable solution I've found is to change the code from using the Child table to the Parent table, which enables the visual to use the Month No. field from the parent:Does work...but only with fields in child (i.e., Month Sequence):&nbsp;Does work...but only when using a parent field (i.e., Month No.):Any ideas on how I can get a "one measure to rule them all" solution that works no matter the context?&nbsp;

Forum Discussion

How do you create a running total using a number series rather than a date?

5 Replies

Related content

Running Maximum

Create alerts when Pipeline run fails

Running sum

Running Totals

Pipeline not running