Period to Period Comparison to create dynamic metrics

[zenz]

Hi There, 

I am kind of new to Power BI, and I took an online class about Power Bi, and before posting this question, I even hired a private tutor, but he could not answer my questions. 

I did the research and asked questions before, but with the tweak in logic, I could not figure out how to make it. Thank you advance on your advice and help. 

here is the raw data table: 

p_id	id	dep	lvl_3	rpt_date
99	100	100	G_En	6/14/2024
94	200	106	G_En	6/14/2024
74	400	106	G_En	6/14/2024
89		100	G_En	6/14/2024
99	100	100	G_En	6/21/2024
89	200	100	G_En	6/21/2024
68	300	100	G_En	6/21/2024
74		100	G_En	6/21/2024
99	100	100	G_En	6/28/2024
68	300	100	G_En	6/28/2024
88		107	G_GB	6/28/2024
99	300	106	G_En	6/29/2024
68	500	100	G_En	6/29/2024
88		107	G_GB	6/29/2024

for each non-blank "id" record in the table, I would like to compare it to the same record in a defined prior period (for example, prior 1 week, or prior 2 week). 

I need to create a metric (a visual, maybe a bar chart or table) that will include all the records in both periods. For example (prior 1 week metric), if i want the metric to report as of 6/28, i would like to include all records in both 6/28 and 6/21. The reason is that there will be records that only exist in the prior period. 

Here is the result should look like (using data in 6/21 and 6/14, and aggregate by "dep" and "lvl_3") 

here is the how I can track the flow of records in 6/21 and 6/14: 

I was think that the above can be achieved by using the DAX Measures (m_beg, m_new, etc.), here are definition of each Measures using the numbers in the above example (begin period=6/14, end period=6/21) : 

• m_beg: the total ending number of the prior period (6/14)
• there are 3 records on 6/14, so the sum of m_beg
• 1 ("id" = 100) under "dep"=100 and "lvl_3" = G_En
• 2 ("id" = 200 and 300) under "dep"=106 and "lvl_3" = G_En, please note "dep"=106 does not exist in 6/21. 
• m_new: the total new record number of the current period (6/21) 
• "id" = 300 is a new record, but 100 and 200 are not new. 
• m_new = 1 under "dep"=100 and "lvl_3" = G_En
• m_trxf_in: the record that changed "dep" from prior period to current, and is transferred into the current period
• "id" = 200 is a transfered-in record in current period
• m_trxf_in = 1 under "dep"=100 and "lvl_3" = G_En, please note that the "dep" changed comparing to the m_trxf_out (see below)
• m_trxf_out: the record that changed "dep" from prior period to current, and is transferred out in the prior period  
• "id" = 200 is a transfered-ou record in prior period
• m_trxf_out = -1 under "dep"=106 and "lvl_3" = G_En, please note that the "dep" changed comparing to the m_trxf_in
• m_removed: the record that is removed from the current period  
• "id" = 400 is a removed record in the current period (i.e. it only exists in prior period) 
• m_removed = -1 under "dep"=106 and "lvl_3" = G_En
• m_end: the total ending number of the current period (6/21)
• there are 3 records on 6/21, and now all under "dep"=100 and "lvl_3" = G_En

Similarly, if I do a 2-week comparison (i.e. 6/28 vs 6/14), then here is the result that I want

• the below visiual, I also include the "id"
• the beginning total is 3 (3 records on 6/14), and its ending total is now 2 (2 records on 6/28)
• 2 out of the 3 begining were removed, and 1 new add, and 1 existing, so end with 2 on 6/28.  

[zenz]

@v-yiruan-msft , 

Sorry, also, when i toggle to the 6/28, the visual does not produce the correct value to m_removed. 

raw data ('id'=200 is removed when comparing 6/28 with 6/21):

the pbix visual: 

the expected results: 

[zenz]

Hi @v-yiruan-msft , 

First of all, thank you so so much for helping. This is extremely helpful for me to learn Power BI which is a great tool that I really want to master, and use it to scale the business insight reporting within my company. 

m_new: the logic is that an ‘id’ is consider new when it’s first introduced in the database. In the raw data table, there are 2 example, first is the ‘id’ = 300 on 6/21, and the second one is the ‘id’ = 500 on 6/29.

Please note that the “new” is always comparative, meaning, for example ‘id’ = 300.

• It will be “new”, if we compare 6/21 to 6/14, or 6/28 to 6/14, because ‘id’ =300 does not exist on 6/14.
• It will not be “new”, if we compare 6/28 to 6/21, because ‘id’=300 exists on 6/21.

I looked into your pbix file, and realized that the m_new column incorrectly include ‘id’ = 200 as “new” for 6/21 by adding the 'id' to the visual. Also, the total for m_new column is not producing the correct total.

The m_trxf_in and m_trxf_out are always a pair, they have to offset to 0.

m_trxf_in: the logic is that it only looks at the current period, where

1. the ‘id’ exists in the prior period
2. the ‘id’ record in the prior has a different value in ‘dep’

m_trxf_out: the logic is that it only looks at the prior period, where

1. the ‘id’ exists in the next period
2. the ‘id’ record in the next has a different value ‘dep’

An example is the ‘id’=200 on 6/21, where the ‘dep’ = 100, while the same ‘id’ record on 6/14 is with ‘dep’ = 106.

In the example of comparing 6/21 with 6/14, (if I add the ‘id’ to the visual, see screenshot below), then you can see that:

• the yellow highlighted row has m_beg=0, m_new=0, m_trxf_in=1, m_trxf_out = 0, m_removed = 0, therefore, m_end = 1 ( 0 + 0 + 1 + 0 + 0 = 1)
  • the reason m_beg=0 is that ‘dep’=100 has 0 ‘id’ in it on 6/14 (the beginning), but then gets a transfer-in from ‘dep’=106.
  • therefore, it ends with 1 in ‘dep’=100
• the blue highlighted row has m_beg=1, m_new=0, m_trxf_in=0, m_trxf_out = -1, m_removed = 0, therefore, m_end = 0 ( 1 + 0 + 0 - 1 + 0 = 0)
  • the reason m_beg=1 is that ‘dep’=106 has 1 ‘id’ in it on 6/14 (the beginning), but then gets a transfer-out to ‘dep’=100.
  • therefore, it ends with 0 in ‘dep’=106

Again, really appreciate your help. thank you, thank you, thank you! 

[v-yiruan-msft]

Hi @zenz ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table(DO NOT create any relationship with the fact table)

Date = VALUES('Table'[rpt_date])

2. Create the measures as below

m_beg = var _enddate=SELECTEDVALUE('Date'[rpt_date])
var _begdate=_enddate-7
return  CALCULATE(COUNT('Table'[p_id]),FILTER('Table',NOT(ISBLANK('Table'[id]))&&'Table'[rpt_date]=_begdate))

m_removed = var _enddate=SELECTEDVALUE('Date'[rpt_date])
var _begdate=_enddate-7
var _bpids=CALCULATETABLE(VALUES('Table'[p_id]), FILTER('Table',NOT(ISBLANK('Table'[id]))&&'Table'[rpt_date]=_begdate ) )
var _epids=CALCULATETABLE(VALUES('Table'[id]),FILTER('Table',NOT(ISBLANK('Table'[id]))&&'Table'[rpt_date]=_enddate ))
return -COUNTROWS(FILTER(ALLSELECTED('Table'), ISBLANK('Table'[id]) &&'Table'[rpt_date]=_enddate &&'Table'[p_id] in _bpids))

m_end = var _enddate=SELECTEDVALUE('Date'[rpt_date])
return  CALCULATE(COUNT('Table'[p_id]),FILTER('Table',NOT(ISBLANK('Table'[id]))&&'Table'[rpt_date]=_enddate ))

In addition, Could you please provide more details(exact samples, screenshot etc.) on the calculation logic of below fields(m_new, m_trxf_in and m_trxf_out)? 

Best Regards