Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Hi @arpost
Did you try this?
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?
Replace it to both
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):
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |