Tracking Movement of Variance between months

Power BI Data Model
1

I have dataset as shown below -

image

My problem statement is to obtain the following result set -

image

Data in the ‘Movement’ column is obtained in the following manner. Let’s take one example of Division Code - 200.

  1. Lookup all data in current month i.e. 31-May for DivisionCode - 200. In this case that would be -
    image
  2. Lookup Amount value in prior month (30-Apr) for combination of CustomerCode and SourceSystem shown in step 1 above and total it. Basically, prior month’s value is being sought. In this case that would be total of 50

image
3. Movement would be (50 - 10 = 40 ) … Step(2) - Step(1)

Additionally if someone drills down on 40, I should be getting all the rows from step (1) and Step(2).

Can someone please guide me in how to solve this problem?

2

Hi vkarthik21,
I suggest a few things.

  1. Make sure you have a DateTable in your model. If not, one is available in the Resource Pack here at EDNA.Power BI Date Table code.txt (2.8 KB)
    Create a relationship between DateTable and MainTable where MainTable is the name of your first screenshot in this post
  2. Calculate the Total Amount = SUMX(‘MainTable’,'MainTable[Amount])
  3. Calculate the Month to Date amount, then the previous Month Amount and a difference between these two.
    I could help more I had the excel spreadsheet.
    On another note, is the data in the screenshots correct? If I understand correct, the Division code 400 Retail has an amount of 20 in April and 10 in May. Shouldn’t the Movement be -10? Same with 300 Bank, 10 in April and 15 in May (Movement 5) or I am not getting right your question?
    Thanks
3

Hi @vkarthik21 , did the response provided by @sorinlinx help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @vkarthik21, we’ve noticed that no response has been received from you since the 7th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @vkarthik21, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!