Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Raneesh
Frequent Visitor

How to get previous week with an another measure

Tying to get previouse week penetration, I'm getting error. Could you please assist.

 

#Prior Week Penetration =
var _Penetration=DIVIDE( [#Count of customer] , 'Occupancy tracker'[#Occupancy] ,0)
return
IF(ISBLANK(_Penetration),0,_Penetration)
var PriorWeek = SELECTEDVALUE ( _Penetration ) -1

RETURN
CALCULATE(
SUM ( _Penetration ),
ALL ( 'Calendar'[Week of Year] ),
'Calendar'[Week of Year] = PriorWeek
)
1 ACCEPTED 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 )

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

@Raneesh 
[#Count of customer] and [#Occupancy] are coulmns or measures?

Yes.

#Count of customer = var _Customer=DISTINCTCOUNT(leadlist[Unit Number])
return
IF(ISBLANK(_Customer),0,_Customer)
 
#Occupancy = CALCULATE(SUM('Occupancy tracker'[Total Occupied]))

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 Number123
PenetrationCurrent WeekPrevious WeekCurrent WeekPrevious WeekCurrent WeekPrevious Week
Building Name       
Balqis 405495
South Residences 7087118

It still shows zero value.

 

Raneesh_0-1647239232542.png

 

tamerj1
Super User
Super User

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 @tamerj1 , it returning zero

 

Raneesh_0-1647168918281.png

 

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
    )

@tamerj1 

 

Do you want to shift both the numerator and the denominator one week back? Yes. exactly.

 

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 )

Thank you soo much @tamerj1 .

Your extended support resolved my concern. 👍

Even now I'm geetting zero.

Thanks for your effort. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.