Hello, I've been strugling with my problem for 2 weeks now. My background is excel, so i understand calculated columns, but not measures at all. What i'm trying to achieve here is a cumulative sum of a measure. First, let me expose the data :
- 1 table 'History_All' where 1 line = 1 part, and columns are ID / Plant / Engine / Status1 Entry / Status1 Exit / Status2 Entry / Status2 Exit (several status so several columns like the last ones)
- 1 calendar 'Jours' (= 'days' in french) table created from scratch with a column Date = CALENDAR (DATE(2017;1;1); DATE(2018;12;31))
I can make everything work using calculated columns, except that when i try to filter by plants or engine (or both) it does not filter, because i've found no way to use FILTERS involving these columns (hence my slicers are not 'connected' to these columns). When using measures, everything filters automatically when adding slicers
- Show number of parts per day --> Achieved with the help of the measure [get date]
- Show a prevision of parts in each status, which would be :
for days > today, take the last known value, add the average of added parts (with Status1.Entry) per day in this status
This is what i can't achieve because it would involve a recursive sum based on a measure (and not a calculated column).
I managed to get the last value :
Status1 Last = CALCULATE([Status1];Jours[Date]=TODAY())
I managed to get the average of last month :
Added = CALCULATE(counta(History_All[ID]);FILTER(History_All;History_All[Status1.Entry]=[get date]))
Added Average = Calculate(AVERAGEX(Jours;[Added]);DATESINPERIOD(Jours[Date];lastdate(Jours[Date]);-30;DAY))
Added Average last = CALCULATE([Added Average];Jours[Date]=TODAY())
I was planning to make a cumulative value of [Added Average last] and add this to [Status1 last] and that would make my prevision line, but that only works in a 'calculated column' state of mind (SUMX for example, doesn't work with measure, and my problem is that i don't get why, because i can't picture what a measure is exactly.)
Any help would be appreciated, even a good link to understand what a measure is exactly.