Cumulative count of occurence on date PowerBI?

2
votes

dataset resembles this:

ID          Milestone
id1         01/01/2020
id2         02/01/2020
id3         04/01/2020
id4         null
id5         02/01/2020
id6         12/01/2020

I'm trying to count the number of records have reached the milestone before a certain date, in other words to count a cumulative total of occurences. I currently have this DAX measure:

#Measure = 
calculate(
    COUNTROWS(table), 
    filter(
        table, 
        table[milestone].[Date] <= MAX(table[milestone].[Date])
    )
)+0

But this doesn't seem to give me the desired result:

Picture of table

As you can see, it doesn't add the count of previous occurences to the current date count.

Thanks in advance

EDIT:

An example after using the feedback provided by msta42a : enter image description here

2
powerbidaxpowerbi-desktopcumulative-sumfind-occurrences

2 Answers

2
votes

This work for me:

Measure = calculate(COUNTROWS('Table')
,FILTER(ALL('Table'[Mileston]), not(ISBLANK('Table'[Mileston])) && 'Table'[Mileston]<= 
SELECTEDVALUE('Table'[Mileston])))

enter image description here

1
votes

Your Measure is very close to your requirement. Here below is the adjusted code-

#Measure = 
calculate(
    COUNTROWS(table), 
    filter(
        ALL(table), 
        table[milestone] <= MAX(table[milestone])
    )
)+0