Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
M. thank you. This is exactly what I needed.
@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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |