Need Help Creating a Dynamic 13 Month Count

[hcollins]

Hello, 

This is what my data is like, notice mutliple dates in the date column that are non-contiguous, blanks and repeating names in entity_name, and non-unique amount decimal numbers 

amount	entity_name	date
800.55	ch33se	06/17/2020
100.02		11/09/2021
200	bUrg3r	11/09/2021
200	bUrg3r	12/01/2021

What I am looking for is a line chart of the cumulative sum of distinct entity_name over time for all time and YTD (two charts)

The catch is that an entity_name goes "inactive" after 12 months, meaning I need to cumulatively sum distinct entity_name not including entity_name with a date > 12 months before a given date for all time and YTD

I have tried many things and am at a loss, any help would be appriciated!

[v-yalanwu-msft · Accepted solution]

Hi, @hcollins ;

You could try it.

count = CALCULATE(DISTINCTCOUNT('Table'[entity_name]),FILTER(ALL('Table'),EOMONTH([date],0)>=EOMONTH(MAX('Table'[date]),-13)&&[date]<=MAX('Table'[date])&&[entity_name]<>BLANK()))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hcollins]

M. thank you. This is exactly what I needed. 

[amitchandak]

@hcollins , Not very clear, with help from date table you can have measure like

Rolling 13 = CALCULATE(sum(Table[Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-13,MONTH))

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[hcollins]

Basically I need this measure

measure I need =
CALCULATE(
     DISTINCTCOUNTNOBLANK('table_name'[text_string]), DATESINPERIOD('table_name'[date_column],TODAY(),-13,MONTH)

except I need more than one value for the TODAY() bit, you have it written as MAX(table_name[date_column]

So I need the rolling 13 month distinctcount of the text string entity_name (sorry the table got fuzzy at the top) for every day all time.

10/11: number of all distinct entity_name in the past 13 months ending on 10/11

10/12: number of all distinct entity_name in the past 13 months ending on 10/12

10/13: number of all distinct entity_name in the past 13 months ending on 10/13

so on and so forth 

Does that make more sense?