cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

How to calculate cumulative values by daily

Hi there, 

I would like to set DAX to calculate daily cumulative session value.
Please let me know how I can do it.  I wrote the situation and what i want to do as below.

<Situation> 
I have a data table which has session of web site by daily bases(Table 1 below).  I would like to add one column to calculate session to pile it up.  And want to reset the cumulative session values at 1st day of month(Table 2 below)

- Table 1 -
Date|Year |Month |Day|Area|Session|
2018/1/1|2018|1|1|US|10000
2018/1/2|2018|1|2|US|10000
2018/1/3|2018|1|3|US|10000

-Table2-
Date|Year|Month|Day|Area|Session|Cumulative Session
2018/1/1|2018|1|1|US|10000|10000
2018/1/2|2018|1|2|US|10000|20000
2018/1/3|2018|1|3|US|10000|30000
...
2018/1/31|2018|1|31|US|10000|310000
2018/2/1|2018|2|1|US|10000|10000
2018/2/2|2018|2|2|US|10000|20000

 

Please give your advice!!

1 ACCEPTED SOLUTION

I got a solution for this.

 

Create measure as MTD session(MTD TTL Session) first, then, Create new measure as  below. 

 

Monthly Cumulative Session =
CALCULATE([MTD TTL Session],
FILTER(ALLSELECTED([Date]),[Date] <= MAX([Date])))

 

 

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Have you tried the running total Quick Measure? Or, you could try my Time Intelligence The Hard Way in the Quick Measure Gallery (it's really not that hard).

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Greg,

 

Thanks for your advice but running total quick measure does not wark well.

because we want to rest to cumulative calcuration every 1st date of month.   it seems that quick measure can not do this.

 

And I want to fitler Area.  Area clumn has like US, UK and DE.

So, I want to do this ...("[]" is condition)

[Sum] daily sessions 

[Between] First date of the month to Date on the row

[Group By] Area, Year, Month

 

do you have any great solution?

 

Thanks 

I got a solution for this.

 

Create measure as MTD session(MTD TTL Session) first, then, Create new measure as  below. 

 

Monthly Cumulative Session =
CALCULATE([MTD TTL Session],
FILTER(ALLSELECTED([Date]),[Date] <= MAX([Date])))

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors