How to build a time series line chart that only sums groups with complete data over date range

[ojt212]

I have population data grouped by state/county/date, where the first and last (non-null) date for each state/county may vary. For instance, county AL/Autauga may have populations {2020-01-01: 105, ... 2021-06-30: 89} while county AL/Baldwin may have populations {2020-03-04: 596, ... 2021-06-30: 599}.

The problem with building a simple line chart off of this data is that the total population "grows" over time as new data is added, as in the screenshot below.

Is there a way to specify that the sum should only include counties for which data is "complete" over the date range (i.e. spans the full date range)?

[v-janeyg-msft · Accepted solution]

Hi, @ojt212 

Yes, you can. You have two choices, One is to put the column that containing "complete" as a slicer, then choice 'complete'. Two is to create a measure instead of the value you put in the visual.

Like:

calculate(sum(table[column]),table[column]="complete")

If it doesn't solve your problem, you can feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.