Plot the difference between two measures on single plot using single date axis

[Inubot]

Dear community,

I am working on a billing system that allows users to allocate credits to their accounts. The allocation and usage of the credits are stored in the DB with date timestamps. I have two tables, Credits and Usage, which have no relationships.

Making use of measures, I was able to plot cumulative step plots to show when credit was allocated and the usage rolled up into weekly intervals, over time.

The two measures are:

TotalCreditsCumulative = 
CALCULATE(
    SUM(Credits[Qty]),
    USERELATIONSHIP(Credits[ProjectId], Projects[Id]),
    FILTER(ALLSELECTED(Credits), Credits[DTC] <= MAX (Credits[DTC]))
)

TotalUsageCumulative = 
CALCULATE(
    SUM(Usage[Price]),
    USERELATIONSHIP(Usage[ServiceId], Services[Id]),
    FILTER(ALLSELECTED(Usage), Usage[DTC] <= MAX (Usage[DTC]))
)

To explain the relationships:

• Credits are allocated to Projects: Credits[ProjectId] <-> Projects[Id]
• Services are linked to Projects: Services[ProjectId] <-> Project[Id]
• Usage is incurred on a service: Usage[ServiceId] <-> Service[Id] (Usage is deducted from the Service's Project credits)

The resulting plots are shown below.

I would like to plot the difference between the two measures as a 'Running Balance'. I have tried adding a third measure, RunningBalance, defined as:

RunningBalance = CALCULATE([TotalCreditsCumulative] - [TotalUsageCumulative])

and tried plotting this over time. I'm expecting to see the 'Running Balance' curve, however, I am getting this plot:

It seems like the dates may be out of sync for the measures. So my question is whether using the measures approach is correct or not. How do I link the dates? Should a table be created instead to store the balance values at certain dates (I would like to avoid this)? 

Any help/suggestions will greatly be appreciated!

[Anonymous · Accepted solution]

Hi @Inubot ,

According to your description, here are my steps you can follow as a solution.

(1) Create a new date table with a continuous range of dates that cover the entire period of your data.

DateTable = CALENDAR(MIN(Credits[DTC]), MAX(Usage[DTC]))

(2) Create relationships between the new date table and both the Credits and Usage tables.

(3)Update your measures to use the new date table:

TotalCreditsCumulative = 
CALCULATE(
    SUM(Credits[Qty]),
    USERELATIONSHIP(Credits[ProjectId], Projects[Id]),
    FILTER(ALLSELECTED(DateTable), DateTable[Date] <= MAX (DateTable[Date]))
)

TotalUsageCumulative = 
CALCULATE(
    SUM(Usage[Price]),
    USERELATIONSHIP(Usage[ServiceId], Services[Id]),
    FILTER(ALLSELECTED(DateTable), DateTable[Date] <= MAX (DateTable[Date]))
)

RunningBalance = [TotalCreditsCumulative] - [TotalUsageCumulative]

(4)Plot the RunningBalance measure using the date column from the DateTable.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@Inubot , you should use a common date table joined to date of your tables and the cumulative measure will look like

make sure field from date table is used on axis

example measures

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.