Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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])))
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).
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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |