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
I cannot work out where i am going wrong with the following rolling 12 months measure. See calculate column measure i would like to keep the current measure and amend if possible...
For Jan 19 The result is correct 60%
For Feb 19 The result is worng and should be the Sum of (81,788,374+94393580) / (135,605,381 + 137602264) = 64%
not (94393580) / (137602264) which the measure is returning.
See expected result in excel image
Sample PBIX
Sample Data
https://www.dropbox.com/s/e280kz8rl9x0i4u/SSAMPLE.pbix?dl=0
Solved! Go to Solution.
Hi @Anonymous ,
The result for row 8 should be 64.15%.
(81,788,374+94,393,580+98,235,790)/(135,605,381+137,602,264+154,585,590)=0.6415
Column =
VAR _res =
CALCULATE (
SUM ( 'Llo_DIR_Data'[Result] ),
FILTER (
'Llo_DIR_Data',
[Date] <= EARLIER ( Llo_DIR_Data[Date] )
&& [New Month] = EARLIER ( Llo_DIR_Data[New Month] )
)
)
VAR _apps =
CALCULATE (
SUM ( 'Llo_DIR_Data'[Apps in Month/Term] ),
FILTER (
'Llo_DIR_Data',
[Date] <= EARLIER ( Llo_DIR_Data[Date] )
&& [New Month] = EARLIER ( Llo_DIR_Data[New Month] )
)
)
RETURN
DIVIDE ( _res, _apps )
I filtered out the blanks in the Power Query editor beforehand.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You mention that you want a measure but your sample PBIX has a calculated column. Please clarify.
The source data is a bit confusing. Can you maybe show the expected result for row 8 ?
Apologies i want to keep the calculated column measure....result for row 8 is 64.73
Hi @Anonymous ,
The result for row 8 should be 64.15%.
(81,788,374+94,393,580+98,235,790)/(135,605,381+137,602,264+154,585,590)=0.6415
Column =
VAR _res =
CALCULATE (
SUM ( 'Llo_DIR_Data'[Result] ),
FILTER (
'Llo_DIR_Data',
[Date] <= EARLIER ( Llo_DIR_Data[Date] )
&& [New Month] = EARLIER ( Llo_DIR_Data[New Month] )
)
)
VAR _apps =
CALCULATE (
SUM ( 'Llo_DIR_Data'[Apps in Month/Term] ),
FILTER (
'Llo_DIR_Data',
[Date] <= EARLIER ( Llo_DIR_Data[Date] )
&& [New Month] = EARLIER ( Llo_DIR_Data[New Month] )
)
)
RETURN
DIVIDE ( _res, _apps )
I filtered out the blanks in the Power Query editor beforehand.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |