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.
Tying to get previouse week penetration, I'm getting error. Could you please assist.
Solved! Go to Solution.
Hi @Raneesh
Actually the formula I have messaged you was almost correct, the only mistake is switching between Numerator and Denominator. This one shall work
%Previous Week Penetration =
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
VAR Customers =
CALCULATE (
DISTINCTCOUNT ( leadlist[Unit Number] ),
'Calendar'[Week of Year] = PriorWeek
)
VAR Occupancy =
CALCULATE (
SUM ( 'Occupancy tracker'[Total Occupied] ),
'Calendar'[Week of Year] = PriorWeek
)
RETURN
DIVIDE ( Customers, Occupancy )
Yes.
Then this should work
#Prior Week Penetration =
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
RETURN
CALCULATE (
DIVIDE ( [#Count of customer], [#Occupancy], 0 ),
'Calendar'[Week of Year] = PriorWeek
)
I'm expecting an output like below.
Week Number | 1 | 2 | 3 | ||||
Penetration | Current Week | Previous Week | Current Week | Previous Week | Current Week | Previous Week | |
Building Name | |||||||
Balqis | 4 | 0 | 5 | 4 | 9 | 5 | |
South Residences | 7 | 0 | 8 | 7 | 11 | 8 |
It still shows zero value.
Hi @Raneesh
You can try
#Prior Week Penetration =
VAR _Penetration =
DIVIDE ( [#Count of customer], 'Occupancy tracker'[#Occupancy], 0 )
VAR _PenetrationChecked =
IF ( ISBLANK ( _Penetration ), 0, _Penetration )
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
RETURN
CALCULATE (
_PenetrationChecked,
REMOVEFILTERS ( 'Calendar' ),
'Calendar'[Week of Year] = PriorWeek
)
Hi @Raneesh
seems I forgot to use SUM
#Prior Week Penetration =
VAR Occupancy =
SUM ( 'Occupancy tracker'[#Occupancy] )
VAR _Penetration =
DIVIDE ( [#Count of customer], Occupancy, 0 )
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
RETURN
CALCULATE (
_Penetration,
REMOVEFILTERS ( 'Calendar' ),
'Calendar'[Week of Year] = PriorWeek
)
Hi @Raneesh
Actually the formula I have messaged you was almost correct, the only mistake is switching between Numerator and Denominator. This one shall work
%Previous Week Penetration =
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
VAR Customers =
CALCULATE (
DISTINCTCOUNT ( leadlist[Unit Number] ),
'Calendar'[Week of Year] = PriorWeek
)
VAR Occupancy =
CALCULATE (
SUM ( 'Occupancy tracker'[Total Occupied] ),
'Calendar'[Week of Year] = PriorWeek
)
RETURN
DIVIDE ( Customers, Occupancy )
Even now I'm geetting zero.
Thanks for your effort.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |