[DAX] Visualize last N months picking only one from filter context

[ibarrau]

Requirement

The idea of our final user is to select one month from specific year and see the last three months of data from a measure including the selected month in a bar chart. Any Power Bi Developer that reads the requirement knows that we have a range filter or multiple selector to show 4 wished months in a bar char visualization. However the request is a bit more creative. Like I said before they want a way to select one specific Year-Month value and watch the last 3 months including the current one. All of us know that the filter context won't let us handle dynamic axis in that way. The expected result will be something like this:

The final expose of the visualizations can be done in different ways. I have been asked to show it same page, drillthrough and even tooltip. The solution for all those three presentations is the same in model and coding. The filter/row context in Power Bi won't let us play with axis inside visualizations because it only allow columns and not measures. This is wht we can't solve it with only one Calendar Table. If we filter an specific year and month the table will be filtered by those even if we try ignoring context or use ALL function and stuff like that. Knowing we can only play with the Value box of a visualization let's use that one in addition of a second Calendar or Date Table. 

The table won't be related with any other because we need to ignore/exclude the context of the axis and control it from the DAX value. In order to create this new table let's first say that in data model the earlier we prepare the data the best it performs. If you can build it in the warehouse or database engine do it there. In this post I'm showing examples for Power Query and DAX.

NOTE: "TablaFecha" is the Calendar/Date Table.

Example PQ

let
   Source = TablaFecha[[Fecha]],
   #"Filtered Rows" = Table.SelectRows(Source, each Date.Day([Fecha]) = 1 ),
   #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Periodo", each Text.From(Date.Year([Fecha])) & Date.ToText([Fecha],"MM"), type text),
   #"Inserted Month Name" = Table.AddColumn(#"Added Custom", "Month Name", each Text.Range(Date.MonthName([Fecha]),0,3) & " " & Text.From(Date.Year([Fecha])) , type text)
in
   #"Inserted Month Name"

Example DAX

FechaDAX =
SELECTCOLUMNS(
   ADDCOLUMNS(
       FILTER( TablaFecha, TablaFecha[Dia]=1 )
       , "@Period", FORMAT(TablaFecha[Fecha], "YYYYMM")
   ), "Fecha", TablaFecha[Fecha], "Period", [@Period]
)

Like I have said before the way of showing this will depend on the requirement. The first picture shows a chart with axis in format YYYYMM but it can be any other date format we want like the column "Month Name" in the PQ example that will render "Jan 2021". We can use any format we want as long as we sort it by the first date column. Our final visualization will be built by Periodo column from this new auxiliar table and a measure that will solve the relation between tables as matter of saying. Let's see two ways of solving the issue.

Solution 1

This is the most visual and easy to understand way because it will ask for each month value literally. Knowing that we want four results we could build four measures. 

• Actual Selected Month
• Selected Month -1
• Selected Month -2
• Selected Month -3

This is like a calculation group or a unit selector we could build. The measure will return values for the matchs and return blank for the rest of the table to avoid the visibility of the unwanted months.

SwitchSolution =
VAR __fecha = MIN(TablaFecha[Fecha])
VAR __actual = SELECTEDVALUE(FechaDAX[Period])
RETURN
SWITCH(__actual,
   FORMAT(__fecha, "YYYYMM"), [Actual Selected Month],
   FORMAT(EDATE(__fecha,-1), "YYYYMM"), [Selected Month -1],
   FORMAT(EDATE(__fecha,-2), "YYYYMM"), [Selected Month -2],
   FORMAT(EDATE(__fecha,-3), "YYYYMM"), [Selected Month -3]
)

The measure will run it for all values in the auxiliar table because it's not related with anything. The switch will help us to only calculate values for the four wished dates depending on the variable __fecha capturing the selected date. All the other rows in the auxiliar table will be blank and blank values are not shown in Power Bi by default.

This is a solution at the end of the day. However it contains some disadventages. Building this solution is demanding us to create a new measure for each number of last months we want to show and edit the last measure. If we suddenly want 9 months it would be quite tedious to change it. This is not very efficient to keep flexibility in the model and it's not the best way to do it. Even though I wanted to show it first in order to give you a picture on how to handle this situations logically with DAX to help understand the "best way".

Solution 2

We might think we have a solution and our user is happy. Now that we can breathe after the deadline we want to make it better, efficient, the best way. This second solution is even more creative because it will create less measures and won't need to change so much if we want more or less months. There is a requirement for this solution to success. We must have a column "Start of Month" in the calendar or date table. If you don't know what is that column just picture that it will be the same value for each day of a month in a year. For example for January 2021 it will always show something like "2021-01-01" even if the row is the 3rd day.

Let's check this amazing DAX out: 

FinalDAX =
VAR __fechaFin = MIN(TablaFecha[Fecha])
VAR __fechaIn = EDATE(__fechaFin,-3)
VAR __actual = SELECTEDVALUE(FechaDAX[Fecha])
RETURN
CALCULATE(
   [MEASURE]
   // Filter for the N months expected (four in the example)
   , DATESBETWEEN(TablaFecha[Start of Month], __fechaIn, __fechaFin)
   // Flter to syncronize the calculation between dates
   , TablaFecha[Start of Month] = __actual
   // Filter to ignore the context of the selected year month against the measure
   , REMOVEFILTERS(TablaFecha)
)

Our new measure will require only one manual interacción if we want to extend or reduce the months to check that is in the __fechaIn variable. If we change -3 to -9 we will have the nine months we wanted changing one single char. This new code will call our [Measure] that will check for last N months only one time calculating the values for the range of dates (start of months). First we are making sure that the values we want to calculate are in the range of dates that we want (e.g. four months from the selection) using start of month because we want monthly values and that value will only have four results for all the 120 days in the calendar table. Then we syncronize the non related tables to make sure the calculation will only be solve for the dates we need in both tables. Finally we need to ignore in our measure the selected year-month date from the slicer to let us handle the way the tables are related in the current filter expressions inside the calculate. This is how we manage to get values of our measure only for the last N months from the selected date. The following picture shows how it should work.

Alright, now we have the most flexible solution working and our user is happy. I hope this help you brainstorm UX solutions for your final users. This example is built to open our minds and see if we can explode the Power Bi desktop even more and don't stay at "you can't, the only way is picking each month". We all probably have more crazy ideas to show even more creative solutions in Power Bi. 

Original post: https://blog.ladataweb.com.ar/post/651536546038726656/dax-visualizar-n-meses-seleccionando-uno