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.
Hello,
I am trying to build a calculation that will give me a running total over the list six terms as of each term.
My data is set up as follows:
What I would like to do is:
For 19AA6, sum my measure for term axis 0 through -5
For 19AA5, sum my measure for term axis -1 through -6
and so on.
Solved! Go to Solution.
Hi @heidibb ,
You can try to create measure or column like DAX below.
Measure1= Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-5)) Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-4)) Return SWITCH(MAX(Success[Code]),"AA",A6, "BB",B5) Column1= Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-5)) Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-4)) Return SWITCH(Success[Code],"AA",A6, "BB",B5)
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @heidibb ,
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
Hi @heidibb ,
You can try to create measure or column like DAX below.
Measure1= Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-5)) Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-4)) Return SWITCH(MAX(Success[Code]),"AA",A6, "BB",B5) Column1= Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-5)) Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-4)) Return SWITCH(Success[Code],"AA",A6, "BB",B5)
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think it would be easier/more clear if you used a calculated column instsead:
VAR current = [Axis] RETURN CALCULATE(SUM(Table), FILTER(Table, [Axis] > current -6 && [axis] <= current))
Thank you. Still having a little trouble with this. I used a calculated column with this version of your suggestion:
You don't have the VAR. Without it [TermAxis] is relative to the filtering context. The statement as you wrote it is:
Filter out every row where [TermAxis] is less than or equal to itself and more than itself minus 6.
GOT IT!
Thank you! I have one more level of complexity.
Is there a way to add an add'l filter to this? For example:
Periods that are "AA" i want to sum the last 6
Periods that are "BB" i want to sum the last 5
I do have a field in my data for period code (AA or BB)
I've got it for the AA, but not sure how to add in the BB to the same calc:
Sure, just use:
IF(Success[Code] = "AA",<true logic>, <false logic>)
<false logic> can also include an IF
If you have a lot of these use a SWITCH
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |