Running-totals problem when plotted on a Calendar-based-date set

[Mike_Mace]

I have three values with deliverables on specific dates. I need to show all three running-total curves on the same graph. I create a calendar-based measure to create relationships for all three measures so that I can use the Calendar-date-set on graph X-axis. When I plot the running totals against the calendar-based set, they do not show as running totals anymore, they show as daliverables by day.

Steps i take:

1. Create Running Totals for each of the 3x values, Planned, Actual, and Recovery. For the Running Totals I use quick measure calculation; Sum of Planned at Base value and Planned Date at Field
2. Create Date measure and mark it as the date in model view.
3. Create Relationships between Date and the date set from each value, Planned Date, Actual Date, Recovery Date (Forecast)
4. Plot a single line graph with Date (calendar) on Axis and the Running totals to have all three curves against the same date set

Showing in snips.

Snip:

1. Planned info: Table showing Planned date, Planned (per date), Running Totals for Planned
2. Date alignment: Table shows Planned date, Planned (per date), Date (calendar) aligning neatly with Planned date. This is proof that the relationship works.
3. Running info against date misalignment: When I plot Running Total against Date (calendar) the Running totals are actually not running anymore. They go back to Planned (per date).
4. All 3x Running curves: Each Running column plotted against each own data set. This is what the curves need to look like. These are the curves that I am trying to have on one graph
5. Planned & Actual Running against Planned Date: This is an experiment. I created a relationship between planned dates and actual dates. Planned curve and Actual curve as per Snip 4. show properly when plotted against their own data sets. Adding Actuals on Planned Curve with Planned dates, shows distorted as you can see. Not the 1, 3, 4 running total. It shows a 2 and 1 In decline.

1. Planned info2. date alignment3. running info against date missalignment4. All 3x Running curves5. Planned & Actual Running against Planned Date

[v-lili6-msft · Accepted solution]

hi  @Mike_Mace 

For your case, you could create three relationship between Calendar table and data table at the same time.

But only one relationship is active, or you could set all them are inactive.

For example:

here is a simple sample.

Then create three measure and use USERELATIONSHIP Function in the formula

REAL AC = CALCULATE(SUM('Table'[PESO]),FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

PLAN AC = CALCULATE(SUM('Table'[PESO]),USERELATIONSHIP('Calendar'[Date],'Table'[PECHA PLAN]),FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

Now drag date filed from Calendar table and these measure into a line visual.

here is sample pbix file, please try it.

Regards,

Lin

[parry2k]

@Mike_Mace so when you created relationship between your data table with date table, one relationship is active and other must be inactive, because you can have only one relationship active between two tables. For the date columns which have inactive relationship, you need to use userrelationship function in your measure to make inactive relation active and after that if you drop date from date table on x-axis and all measures, you will see correct information.

Would appreciate Kudos 🙂 if my solution helped.

[Mike_Mace]

Appreciate the point @parry2k  on inactive relationships

Issue is, even if i use only one relationship between Date set 1 and calendar dates the information still shows distorted, snip 3.

If you help me with this I'll get the whole office to give you kudos 🙂

[parry2k]

@Mike_Mace send me your pbix with email and I will get it done. remove sensitive information before sharing. my email address is in footer. That is going to be faster and quicker way to get you over the line. cheers!!