Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
User | Count |
---|---|
17 | |
14 | |
13 | |
13 | |
12 |
User | Count |
---|---|
19 | |
14 | |
13 | |
10 | |
9 |