how to calculate or measure two values from two different table Week-wise

[goyalsa]

Hi PBI Community,

I am stuck on this step and could not figure out how to resolve it. Your suggestion/help will be much appreciated.

The attached screenshot shows two matrices, One shows Demand and the other shows Supply, Now I am stuck on how to calculate/measure demand-supply (Week-wise) and shows in it the table.

Many Thanks in Advance !!!

[goyalsa]

Hi @AllisonKennedy ,

Thanks for your input. It is working great. 

Cheers!!

[AllisonKennedy]

@goyalsa  What does your data model look like? 

Assuming you have a DimDate table that is connected to both the Supply and Demand, you should be able to do: 

Variance = SUM(Supply[Value]) - SUM(Demand[Value]) 

Then put it in a matrix with DimDate[WeekNumber]

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

[goyalsa]

Hi @AllisonKennedy 

Thanks for the solution, I managed to calculate Supply and demand separately and put them on another matrix. But the stuck part is:

How to calculate:

Total supply at week 202130 - Total demand at week 202130 = Balance

  Then,

For Week 202131,

Balance from week 202130 + Total supply at week 202131 - Total demand at week 202131 = Balance

   Then,

Same for a further week, Like Week 202132

Balance from week 202131 + total supply at week 202132 - Total demand at week 202132 = Balance

Your help will be much appreciated !!

[AllisonKennedy · Accepted solution]

@goyalsa 

This may be tricky - how do you define the start of your balance? Basically sounds like a running total calculation?

Try a new Measure:

Diff = [_Total Supply Measure] - [_Total Demand Measure]

Then do running total of Diff:

Balance =

VAR _week = MAX(Date[WeekNum]

RETURN

CALCULATE([Diff], ALL(Date), Date[WeekNum] <= _week )