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.
Go to Solution.
@echow Add a new column as :
This is the new Measure:
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 🙂
View solution in original post
@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-bihttps://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.
Proud to be a Super User!
try a measure
var CurrentDate = MAX(Table[Date])
CALCULATE(SUM(Table[Mbr Count]), Table[Date] <= CurrentDate )
@echow Try this measure
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
@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.
Is the measure your cummulative?
So by right it should be
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.
Check out the on demand sessions that are available now!
Check out the Winners!
Features releasing from October 2020 through March 2021