Forum Discussion
Caputure monthly changes for Unique Text and Numbers
Hello,
I have the following scenario: for any 2 continuous periods selected in filter, I would like to identify a) the Primary Keys that have a Market ID change in the next period (independent of any volume change, if there is one), and b) the Volume change (independent of the Market ID change, if there is one). The last 2 columns below are the desired output. How can I accomplish this in DAX?
Thank you.
| Period | Primary Key | Market | Volume | Change/No Change of Market | Volume change |
| 2021-06 | abc | A | 1.5 | change | 0 |
| 2021-07 | abc | B | 1.7 | change | 0.2 |
| 2021-06 | xyz | C | 2.2 | no change | 0 |
| 2021-07 | xyz | C | 2.3 | no change | 0.1 |
| 2021-06 | def | D | 3.4 | change | 0 |
| 2021-07 | def | E | 3.9 | change | 0.5 |
tomekm See Page 2 of attached PBIX below signature.
5 Replies
tomekm Start with 2 disconnected tables of your market periods. You can get these using this code:
Slicer Table 1 = DISTINCT(SELECTCOLUMNS('Table',"Period",[Period])) Slicer Table 2 = DISTINCT(SELECTCOLUMNS('Table',"Period",[Period]))Next, create a measure like this for the change:
Change No Change of Market Measure = VAR __PrimaryKey = MAX('Table'[Primary Key]) VAR __Period1 = MAX('Slicer Table 1'[Period]) VAR __Period2 = MAX('Slicer Table 2'[Period]) VAR __Market1 = MAXX(FILTER(ALL('Table'),[Primary Key] = __PrimaryKey && [Period] = __Period1),[Market]) VAR __Market2 = MAXX(FILTER(ALL('Table'),[Primary Key] = __PrimaryKey && [Period] = __Period2),[Market]) RETURN IF(__Market1 = __Market2,"change","no change")The Volume Change is almost the same formula obviously.
Hi Greg,
Thank you for the reply. I've been trying to replicate these steps but I'm not getting the results unfortunately/not sure what I'm doing wrong. Would you be able to attach a sample PBI workbook based on my sample data above, so I can see how this works and see the 2 new columns?
I appreciate it!
