Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arpost
Post Patron
Post Patron

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

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:

AllocDateStartDateSalesClientMonth Sequence
1/1/202112/1/2020100AcmeTech1
1/2/202112/1/2020100AcmeTech1
2/1/202112/1/2020200AcmeTech2
4/1/202112/1/2020300AcmeTech4
5/1/202112/1/2020100AcmeTech5
1/1/202010/1/2019500LuxTech3
2/1/202010/1/2019100LuxTech4
3/4/202010/1/2019200LuxTech5
5/2/202110/1/2019300LuxTech19

 

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

arpost_1-1630702330458.png

 

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:

arpost_2-1630702519206.png

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.

 

 

5 REPLIES 5
v-henryk-mstf
Community Support
Community Support

Hi @arpost ,

 

Is @aj1973  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

 

aj1973
Community Champion
Community Champion

Hi @arpost 

Did you try this?

aj1973_0-1630705330895.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

@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?

arpost_0-1630708283805.png

 

aj1973
Community Champion
Community Champion

Replace it to both

aj1973_0-1630710123596.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Thanks for the reply, @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):

arpost_3-1631191314918.png

 

Does work...but only when using a parent field (i.e., Month No.):

arpost_4-1631191413409.png

Any ideas on how I can get a "one measure to rule them all" solution that works no matter the context?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.