cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Accepted Solutions
Highlighted
Community Champion
Community Champion

@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 

View solution in original post

9 REPLIES 9
Highlighted
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

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
Highlighted
Community Champion
Community Champion

@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

Highlighted

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

Highlighted

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

Highlighted
Community Champion
Community Champion

@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

 

Highlighted

@Fowmy 

Is the measure your cummulative?

 

So by right it should be 

echow_0-1596010763372.png

 

Highlighted
Community Champion
Community Champion

@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 

View solution in original post

Highlighted

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors