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.
I am trying to count distinct values in a by date and create a new measure that calculates the cumulative sum of the distinct counts as the time period progresses: The table on the left is representative of my data table. The table on the right is what I would like to my counts to look like.
Any help you can provide is appreciated!!
Solved! Go to Solution.
Assuming you have a calendar table
https://exceleratorbi.com.au/power-pivot-calendar-tables/
something like this
Distinct products =DISTINCTCOUNT(data[product])
Running total =CALCULATE(sumx(values(calendar[date]),[Distinct Products]),Filter(All(calendar),calendar[date] <= max(calendar[date])))
use the calendar date column in your visual.
Hi @Anonymous ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards,
Icey
HI @Anonymous
I used the below to get the running total.
CALCULATE (
SUM ( 'Table'[Count Distinct Product] ),
ALL ( 'Table' ),
'Table'[Date] < EARLIER ( 'Table'[Date] )
)
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Assuming you have a calendar table
https://exceleratorbi.com.au/power-pivot-calendar-tables/
something like this
Distinct products =DISTINCTCOUNT(data[product])
Running total =CALCULATE(sumx(values(calendar[date]),[Distinct Products]),Filter(All(calendar),calendar[date] <= max(calendar[date])))
use the calendar date column in your visual.
Thank you! This worked.
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 |
---|---|
112 | |
97 | |
78 | |
68 | |
55 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |