How to get previous week with an another measure

[Raneesh]

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

)

[tamerj1]

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

[Raneesh]

Yes.

#Count of customer = var _Customer=DISTINCTCOUNT(leadlist[Unit Number])

return

IF(ISBLANK(_Customer),0,_Customer)

#Occupancy = CALCULATE(SUM('Occupancy tracker'[Total Occupied]))

[tamerj1]

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
    )

[Raneesh]

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

[Raneesh]

It still shows zero value.

[tamerj1]

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
    )

[Raneesh]

Hi @tamerj1 , it returning zero

[tamerj1]

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
    )

[Raneesh]

@tamerj1 

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

[tamerj1 · 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 )

[Raneesh]

Thank you soo much @tamerj1 .

Your extended support resolved my concern. 👍

[Raneesh]

Even now I'm geetting zero.

Thanks for your effort.