How to create a measure to calculate number of new stores?

[Anonymous]

Hello everyone,

I'm using Power BI desktop to conduct retail analysis. I found a great retail analysis sample from power bi service which looks amazing! It shows number of new stores in the report. I'm hoping to create something similar with my own dataset but I have no clue how to create this measure...can anyone help me please?

What I have:

I have 2 sets of data from 2020 and 2021 including transaction amount, store ID number, store address, city, product name etc.

My ask:

1. I want to count number of new stores in 2021 dataset comparing to 2020 so I show the number in a card like the one below.

 

2. I created a table which shows store ID number in rows, 2021 and 2020 are columns. In each of the columns, how can I change the store ID number to 'Yes' and any blank cells as 'No'?

I'm still new to power bi but I'm very willing to learn more. I'd be very grateful if anyone could tell me how to quickly build up Dax skills. Thank you!!!!!

[v-jayw-msft]

Hi @Anonymous ,

You might be able to use DAX formula to do that, please share some sample data and expected result.

Best Regards,

Jay

[Ashish_Mathur]

Hi,

Share some data to work with.  You may either share the download link of your PBI file or share the tables here (in a format that can be pasted in an MS Excel file).

[lbendlin · Accepted solution]

You may want to look into DAX functions like EXCEPT() - if you feed it the list of 2021 stores, and of 2020 stores then it will give you all the stores back that are present in the 2021 data but not the 2020 data.  Use COUNTROWS() to get your number.

You can also switch the inputs around, to get all stores that did business in 2020 but not in 2021  etc.