Zahid's blog on Data Engineering and BI

Let’s get straight into the problems.

Problem #1

You get country sales data on monthly basis. But, some of the countries i.e. Switzerland and Italy lag the others. Let’s imagine a scenario when all countries latest data (June-2020) has arrived except Switzerland and Italy. This is how your data table would look like in this interim situation:

As you can see, Switzerland and Italy are missing from June data.

I have come across many clients who would prefer showing May-2020 in reports as latest data month of Europe region in this situation. Because May-2020 is the latest complete month for Europe. At the same time, they would like to see June-2020/May-2020 whichever is applicable as latest month for individual countries. In other words, client wants to see something like this in his report:

This is our first problem.

Now, let’s get started with implementation.

Let’s imagine our table’s name is CountryData. We will introduce a calculated column “Latest Month Column” using DAX:

Latest Month Column = FORMAT(CALCULATE(MAX(CountryData[Month]), FILTER(CountryData, CountryData[Country]=EARLIER(CountryData[Country]))),“mm/dd/yyyy”)

This column always shows the latest month available for respective country.

Now, we will introduce two DAX measures called “Latest Month” and “Latest Month Sales”

Latest Month = MIN (CountryData[Latest Month Column])

Latest Month Sales = SUMX(FILTER(CountryData, CountryData[Month]= CountryData[Latest Month]),CountryData[Sales])

That’s it. We will now use these two measures in our report:

As you can see, North America’s latest moth is June-2020 whereas Europe’s is May-2020.

Problem #2

This lagging nature of some countries will cause some problem in charts/graphs as well. Look at following charts

First bar chart is fine. I have placed it just to visualize that Switzerland and Italy are missing from June. But, the second one (line chart at right hand side) will give the client a worrying picture of his business. They would see Europe’s sales is suddenly going through a steep decline. It’s a confusing message because the decline was caused due to the absence of Switzerland and Italy. It will get fixed as soon as they arrive. It has nothing to do with actual sales. In this scenario, some clients would ask you to plot the latest month only if it has complete set of data for all countries. In other words, we will plot charts only up to the common maximum month for all countries within that region. In our case, last month plotted for Europe will be May-2020 and North America’s last month will be June-2020. This will avoid the misinformation about Europe’s sales. And when Switzerland and Italy eventually arrive, June-2020 will be plotted for Europe as well. This is our Problem#2.

Let’s implement the solution now:

Step 1: Let’s create a calculated table called “RegionLatestMonth” from our original CountryData table. This will contain region’s name and common maximum month from all its respective countries.

RegionLatestMonth = SUMMARIZE(CountryData,CountryData[Region],“Region’s Latest Month”, [Latest Month])

Step2: Create another table called “CountryData2” that will have one extra column than the original CountryData table. This extra column will have Region’s common maximum month for each country. In other words, we will join our CountryData table with the DAX table created at Step 1 based on Region names and add the first’s table’s column (Region’sLatestMonth) as an extra column into the second table called CountryData. Resultant table’s name is CountryData2:

CountryData2 =

var TempTable = TREATAS (RegionLatestMonth, CountryData[Region], RegionLatestMonth[Region’s Latest Month])

RETURN NATURALLEFTOUTERJOIN (CountryData, TempTable)

Step3: Finally, create another DAX table called CountryData3 from CountryData2 created at Step2 by excluding the rows whose month is larger than respective Region’s common maximum month.

CountryData3 = CALCULATETABLE (CountryData2, FILTER (CountryData2, CountryData2[Month]<=CountryData2[Region’s Latest Month]))

And then use the CountryData3 created at Step3 in your visualization:

Now you can see that Europe’s last month is May-2020 while North America’s last month is June-2020. I have highlighted both in above diagram.