Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |