Yearly cumulative count resetting every month/day

[flyinggnugget]

Hi all, I am trying to get a yearly cumulative count using measure, but the cumulative figure keeps resetting every month/day. Would greatly appreciate some help. I have the following data:

and I am trying to achieve the following result:

Year	Month Name	Day	Cumulative Total
2023	January	1	1
2023	January	2	2
2023	January	3	3
2023	January	4	4
2023	January	5	5
2024	January	1	1
2024	January	2	2
2024	January	3	3
2024	January	4	4
2024	January	5	5
2024	February	1	6
2024	February	2	7
2024	February	3	8
2024	February	4	9
2024	February	5	10

I have tried the following measure:

Measure 6 = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Value 2]),
'Table'[Date] <= MAX('Table'[Date]),
ALL('Table'[Month Name]),
ALL('Table'[Day])
)

but somehow the cumulative value is resetting each new month

[Daniel29195]

@flyinggnugget 

Measure 6 = 
CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Value 2]),
'Table'[Date] <= MAX('Table'[Date]),
ALL('Table'[Month Name]),ALL('Table'[Month Nb])
ALL('Table'[Day])
)

let me know if it works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Greg_Deckler · Accepted solution]

@flyinggnugget Try this: 

Measure 6 = 
  VAR __Date = MAX('Table'[Date])
  VAR __Year = YEAR(__Date)
  VAR __Table = FILTER(ALL('Table'), [Value 2] <> BLANK() && YEAR([Date]) = __Year &&[Date] <= __Date)
  VAR __Result = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table, "__Value", [Value 2])))
RETURN
  __Result

CALCULATE tends to have issues with single table data models:

[flyinggnugget]

Hi @Greg_Deckler ,

Suppose if I were to visualize the results from the measure as a line chart, everything looks good for the current data, as seen below:

However, if lets say I were to remove a data point, e.g. 4th Jan 2024, there is now a a break in the line chart as seen in the following screenshot:

I understand that this is because there's not 4th Jan 2024 data point. I'm thinking that ideally a calendar table should be used, but it's currently not available in the semantic model and furthermore, I have quite a number of different date columns in the fact table. I am wondering if there's a workaround in the DAX to get the 2024 line to join up?

Your help would be very much appreciated. Thank you

[Greg_Deckler]

@flyinggnugget If there is no Jan 4th data point then where is that coming from in the visual?

[flyinggnugget]

@Greg_Deckler In the first scenario there is a 4th Jan data point. I was just wondering if let's say I were to delete the 4th Jan data point in the second scenario, would it be possible to have a workaround to join the 3rd Jan and 5th Jan points

[flyinggnugget]

Thanks for the help. Video was an interesting watch as well!