Filter by last not blank date in Power BI

Question

I have data from multiple countries on a monthly basis. Since the updates are not regular, I want to set up filter to visuals, so they would show the last month for which I have data from all the countries. I have data from each country loaded into a separate dataset, which then are merged into one big. Is there an easy way to place such filter? I managed to use "LASTDATE" function in each of country sets to find which date is last, but if I try to filter with that measure, I simply get nothing in a result. Thanks!

Did you want to show the latest month of data that all countries have in common, or the last month of data for each country, regardless of what you have for the others? — Ryan B.
@RyanB. First one - the last they all have in common. E.g if half countries have March'19, but half Feb'19, I want somehow add visual or page filter that would show data only up to Feb'19. Sorry for being bad explaining. Thanks. — Marqz

Ryan B. Ryan B. · Accepted Answer · 2019-03-12T15:07:19

Well, this feels a little clunky to me but I believe it will work for you. There are two steps. The first is to create a summary table that reads through your data and counts the number of distinct countries that you have in each month. This will be a new table in your model, so go into the modeling tab, click 'New Table' and add this DAX. Obviously, correct for your table and column names.

SUMMARIZED_ROWS = SUMMARIZE(
    'Table1'
    ,Table1[Month]
    ,"CountOfCountries"
    ,DISTINCTCOUNT(Table1[Country])
)

Now add a measure to the table (or anywhere) like this:

MonthWithMostCountries = CALCULATE(
    LASTNONBLANK(SUMMARIZED_ROWS[Month], 1 )
    , FILTER(SUMMARIZED_ROWS, SUMMARIZED_ROWS[CountOfCountries] =  MAX(SUMMARIZED_ROWS[CountOfCountries]) )  )

This is going to give you the month where you have the most distinct countries in your data. You'll want to look at it in a card or similarly isolated visual as it is a measure and can be affected by filter context.

So, on the left is my mock data - 3 countries, 3 months each with a 1 month stagger. On the right you see the result of the Summarize table. Then the measure showing the final result.

Hope it helps.

Filter by last not blank date in Power BI

1 Answers