Power BI: When your data source does not update everyday

When I pull coronavirus data from the CDC site, some data update more frequently than others. The data pertaining to cases and deaths appear to update daily on the afternoon of the next day whereas hospitalization may be updated weekly and testing in some infrequent fashion. The infrequency of update can cause problems with certain Power BI visuals if you want automatic updates.

Here’s an example of visuals for hospitalization at the total US level:

In the upper right-hand corner is the latest date culled from the CDC’s hospitalization data source. Yesterday was the 22nd of March and the last date in the hospitalization data at that time was March 20th. The line and clustered column chart works fine showing all of the daily dates up until March 20th. The “Moving Ave”, “Max” and “Today” visuals found below are cards and out of the three, the “Today” card fails. I could set this card up to filter for a specific date or to filter by relative date, but I don’t want to have to go in everyday and change that filter. I want it to be set once and done.

Here’s the same thing with the CDC testing data except the last date is March 19th:

After about a week or so constantly updating the date filter, I thought, “I can’t stand this”, so I went Googling for help. I ended up creating two measures: one to calculate the last date from the data source and the other the representing a specific value as of the last date.

First Measure

The first measure, calculating the last date found in the data source, is created with the following formula:

Written generically:
Last Date measure = calculate(Max(‘Table name'[date]),All(‘Table name’))

Written for my specific case:
date_latest = calculate(Max(‘CDC Hospitalization'[date]),All(‘CDC Hospitalization’))

Please note I did not use:
date_latest = Max(‘CDC Hospitalization'[date])

While technically that measure worked, when it came time to create the second measure, the second measure calculated the values incorrectly or, in other words, it failed.

Second Measure

The second measure can be written generically as:
Value measure = calculate(Sumx(‘Table name’,’Table name'[value]),filter(‘Table name,’Table name'[date].[Date]=[Last Date measure]))

Where the “value measure” is the number that I want to show up in the card. In this instance, I wanted to show the total number of people being hospitalized on the latest date that I have data for, which was March 20th (not March 22nd).

My second measure actually looks like the following:
zLatest_total_hospital = calculate(Sumx(‘CDC Hospitalization’,’CDC Hospitalization'[total_adult_patients_hospitalized_confirmed_and_suspected_covid]),filter(‘CDC Hospitalization’,’CDC Hospitalization'[date].[Date]=[date_latest]))

So yes, I have to create more measures for cards. I have a measure for showing the total US hospitalization for the latest day I have data for and total US testing numbers for the latest day I have data for (which was March 19th). Here are the final graphics with the actual figures:

Is there another way of doing this? I’m sure there is; I just didn’t find it.

By the way, here’s a site that offers up a solution that looks very similar to what I have written: Power BI User Exchange. I can’t find the web page where I found the two measures that worked for me.