Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Date | Column A | Column B | Column C |
1/2020 | 75 | 4596 | 1.63% |
2/2020 | 66 | 4596 | 1.44% |
3/2020 | 68 | 4612 | 1.47% |
4/2020 | 53 | 4595 | 1.15% |
5/2020 | 46 | 4553 | 1.01% |
6/2020 | 88 | 4494 | 1.96% |
7/2020 | 76 | 4437 | 1.71% |
8/2020 | 83 | 4406 | 1.88% |
9/2020 | 74 | 4394 | 1.68% |
10/2020 | 84 | 4620 | 1.82% |
11/2020 | 87 | 4841 | 1.80% |
12/2020 | 75 | 4823 | 1.56% |
Solved! Go to Solution.
@jbajon , Try a measure like
calculate(sumx(values(Table[date]), calculate(divide(sum(Table[A]),Sum(Table[B])))), filter(AllSelected(Table), Table[date] <=max(Table[date])))
_Test 2 =
var a = CALCULATE(SUM('Table'[Column A]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))
var b = MAXX('Table',MAX('Table'[Column B]))
Return
DIVIDE(a,b)
Proud to be a Super User!
Hi FarhanAhmed,
Thank you for your response. When attempting your solution I was getting an error message that MAX() only accepts column references where [Column B] is actually a measure in this example.
@jbajon , Try a measure like
calculate(sumx(values(Table[date]), calculate(divide(sum(Table[A]),Sum(Table[B])))), filter(AllSelected(Table), Table[date] <=max(Table[date])))
Hi amitchandak,
Thank you for your reponse. I was able to alter your example a bit to get to my final working solution. The only difference I forgot to mention on my actual project that the Year and Month are two different fields (rather than combined into one field) for formatting reasons, so I added an additional filter condition so that the running value restarted each year.
Here is what ultimately worked for me:
calculate(sumx(values('DateTable'[FullDate]), [Column_C]), filter(allselected('DateTable'), 'DateTable'[FullDate_Year] = max('DateTable'[FullDate_Year]) && 'DateTable'[FullDate_Month#] <=max('DateTable'[FullDate_Month#])))
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |