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 calculate a rolling sum of the previous 12M which re-calculates every 2 months.
I have achieved this using a helper column in the Calendar Table named 2M_1 and one measure having multiple nested ifs SalesReset2M
I am now trying to replicate the logic in SalesReset2M but with a simpler formula, but I am failing in making the value replicate itself, given month column being different to 2M_1.
How can I adapt my code below to get the same results as SalesReset2M ?
SalesReset2M_1 = var selectedmonth = if(HASONEVALUE('Date'[2M_1]),VALUES('Date'[2M_1])) return CALCULATE([SalesRolling],ALLEXCEPT('Date','Date'[2M_1]),'Date'[month]=selectedmonth,VALUES('Date'[MonthYear]))
Thanks in advance!
@Anonymous ,
It's because in your filter, you have added this condition "'Date'[month] = selectedmonth", this will compare the values in the two columns [2M_1], [month]. To get the same results as SalesReset2M, just remove the condition like below:
SalesReset2M_1 = CALCULATE ( [SalesRolling], ALLEXCEPT ( 'Date', 'Date'[2M_1] ), VALUES ( 'Date'[MonthYear] ) )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yuta-msft wrote:@Anonymous ,
It's because in your filter, you have added this condition "'Date'[month] = selectedmonth", this will compare the values in the two columns [2M_1], [month]. To get the same results as SalesReset2M, just remove the condition like below:
SalesReset2M_1 = CALCULATE ( [SalesRolling], ALLEXCEPT ( 'Date', 'Date'[2M_1] ), VALUES ( 'Date'[MonthYear] ) )Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey,
Thanks for the reply 🙂 but doing that formula will just calculate the normal Rolling Mesure and not reset every two months like I have in the last column, if you see, the same values repeats itself twice ,this is what I need to achieve.
Jan calculates and Feb gets also the same value
Mar calculates and April gets also the same value
so on and so forth
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 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |