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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
echow
Helper II
Helper II

How to write a DAX for cummulative that does not Reset

How do I create a cummulative member column for the set of data below.

Meaning what I should be getting is 2019-12 is 315, 2020-1 is 1,116 and then 2020-2 is 1,414 and so forth.

 

PowerBi.png

1 ACCEPTED SOLUTION

@echow 

Add a new column as :

This is the new Measure:


Modified Measure.

Measure = 
CALCULATE(
   COUNT('Table'[VALUE]),
    FILTER(ALL('Table'),'Table'[YEARMON]<=MAX('Table'[YEARMON])
))



________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

9 REPLIES 9
Fowmy
Super User
Super User

@echow 

Try this measure

Measure = 
CALCULATE(
    COUNT('Table'[VALUE]),
    FILTER(ALL('Table'),'Table'[YEAR]<=MAX('Table'[YEAR])&& 'Table'[MONTH]<=MAX('Table'[MONTH]))
)

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy  I tried your script but it only start adding the cummulative starting in 2020. It doesn't continue from 2019 December.

@echow 

It works for me, recheck how you have adapted the measure for your model.

Fowmy_0-1596010395243.png



________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy 

Is the measure your cummulative?

 

So by right it should be 

echow_0-1596010763372.png

 

@echow 

Add a new column as :

This is the new Measure:


Modified Measure.

Measure = 
CALCULATE(
   COUNT('Table'[VALUE]),
    FILTER(ALL('Table'),'Table'[YEARMON]<=MAX('Table'[YEARMON])
))



________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thanks @Fowmy 

 

I think what you are suggesting is to combine the year and month together. I think that should work.

But was wondering it can be used ealier using your AND function for year and month.

az38
Community Champion
Community Champion

Hi @echow 

try a measure

Total = 
var CurrentDate = MAX(Table[Date])
RETURN
CALCULATE(SUM(Table[Mbr Count]), Table[Date] <= CurrentDate )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
amitchandak
Super User
Super User

@echow , Try like with date table

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

 

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

Thanks @amitchandak 

 

What happen if I want to do it using Year, Month instead of date?

The row of my table is in the order of Year,Month

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.