Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
flyinggnugget
Frequent Visitor

Yearly cumulative count resetting every month/day

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:

flyinggnugget_0-1707052071210.png

 

and I am trying to achieve the following result:

YearMonth NameDayCumulative Total
2023January11
2023January22
2023January33
2023January44
2023January55
2024January11
2024January22
2024January33
2024January44
2024January55
2024February16
2024February27
2024February38
2024February49
2024February510

 

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

flyinggnugget_1-1707052468548.png

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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:


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Daniel29195
Super User
Super User

@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
Super User
Super User

@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:


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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:

flyinggnugget_0-1707991507654.png

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:

flyinggnugget_1-1707991607396.png

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

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.