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 Experts,
Can someone please help me with below issue. I have data like this:
TableA
SequenceNumber | Year |
130 | 2016 |
131 | 2016 |
132 | 2017 |
133 | 2017 |
134 | 2018 |
135 | 2018 |
136 | 2019 |
Measure [NetGrowth] works good.
My [NetGrowth] cumulative measure is
NetGrowthCumulative = VAR MyYearMin = MINX(TableA,TableA[Year]) VAR MyYearMax = MAXX(TableA,TableA[Year]) RETURN SUMX( FILTER( ALL(TableA),TableA[SequenceNumber]<=MAX(TableA[SequenceNumber]) && TableA[Year]>=MyYearMin && TableA[Year]<=MyYearMax ), [NetGrowth] )
Solved! Go to Solution.
Hello,
I got this working. Hope below solution will help someone:
1. Created a measure in TableA to get selected Year
SelectedYear = VAR t = ALLSELECTED(TableA[Year]) RETURN IF(MAX(TableA[Year]) in t,1,0)
2. Cumulative Measure code
NetGrowthCumulative = SUMX( FILTER( ALLEXCEPT(TableA,TableA[Year]), TableA[SeqNo]<=MAX(TableA[SeqNo]) && [SelectedYear]=1 ), [NetGrowth])
3. Make sure there is relationship between TableA & TableB.
Regards
Hi @um_mir,
What is your meausre [NetGrowth]? Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.
Regards,
Frank
Thank you for your quick response. Please see the link for the pbxi file. I have reproduced the issue. Cummulative sum works for a year and it resets again in the next year. For the attached example NetGrowthCumulative I'm exepecting below result:
Year | SeqNo | NetGrowth | NetGrowthCumulative |
2018 | 1 | 100 | 100 |
2018 | 2 | 50 | 150 |
2018 | 3 | 95 | 245 |
2018 | 4 | -50 | 195 |
2018 | 5 | 120 | 315 |
2019 | 6 | 20 | 335 |
2019 | 7 | 80 | 415 |
Regards
Hello,
I got this working. Hope below solution will help someone:
1. Created a measure in TableA to get selected Year
SelectedYear = VAR t = ALLSELECTED(TableA[Year]) RETURN IF(MAX(TableA[Year]) in t,1,0)
2. Cumulative Measure code
NetGrowthCumulative = SUMX( FILTER( ALLEXCEPT(TableA,TableA[Year]), TableA[SeqNo]<=MAX(TableA[SeqNo]) && [SelectedYear]=1 ), [NetGrowth])
3. Make sure there is relationship between TableA & TableB.
Regards
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |