N Number of Weeks from Current Selection

[Anonymous]

Hi all,

I'm trying to create a chart that displays a churn % on a rolling 52 week basis. I only want the chart to ever display 52 weeks (never more or less), whereby it dynamically changes based on the user selection of year and week number.

i.e if the user selects 2019 and week 30, I want the chart to show from 2019 week 30 backwards 52 weeks.

My churn % syntax is below, incase it needs to be referenced in any replies:

TEST Churn % = DIVIDE(([Lapsed Patients 52 Weeks]+[Left Patients 52 Weeks]),[Active Patients Start Period])

Lapsed Patients 52 Weeks:

Lapsed Patients 52 Weeks = CALCULATE (
    SUM(FIN_Key_Stats_Per_Site[LapsedPatients]),
    DATESBETWEEN (
        MD_Calendar[Date],
        SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( MD_Calendar[Date] ) ) ),
        LASTDATE ( MD_Calendar[Date] )
    )
)

Left Patients 52 Weeks:

Left Patients 52 Weeks = CALCULATE (
    SUM(FIN_Key_Stats_Per_Site[LeftPatients]),
    DATESBETWEEN (
        'MD_Calendar'[Date],
        SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'MD_Calendar'[Date] ) ) ),
        LASTDATE ( 'MD_Calendar'[Date] )
    )
)

Active Patients Start of Period:

Active Patients Start Period = CALCULATE([Active Patients Current Week],SAMEPERIODLASTYEAR(MD_Calendar[Date]))

Active Patients Current Week:

Active Patients Current Week = CALCULATE(SUM(FIN_Key_Stats_Per_Site[ActivePatients]),LASTDATE(FIN_Key_Stats_Per_Site[Date]))

Has anyone ever come across something like this before? Any advice or help would be much appreciated!

Cheers,

Aaron

[v-diye-msft]

Hi @Anonymous 

I created the sample with columns named as your requested. NO relationship between ‘FIN_Key_Stats_Per_Site’ and ‘MD_Calendar’,

Added 2 slicers using below formula in MD table for selecting:

Year = YEAR([Date])
Week = WEEKNUM(MD_Calendar[Date])

And create below measures to generate the results:

Active Patients Current Week = var a = MIN(MD_Calendar[Date])
var b = MAX(MD_Calendar[Date])
Return
CALCULATE(SUM(FIN_Key_Stats_Per_Site[ActivePatients]),FILTER(FIN_Key_Stats_Per_Site,[Date]>=a&&[Date]<=b))

Active Patients Start Period = 
var a = MINX(FILTER(ALL(MD_Calendar),[Year]=SELECTEDVALUE(MD_Calendar[Year])-1&&MD_Calendar[Week]=SELECTEDVALUE(MD_Calendar[Week])),MD_Calendar[Date])
var b = MAXX(FILTER(ALL(MD_Calendar),[Year]=SELECTEDVALUE(MD_Calendar[Year])-1&&MD_Calendar[Week]=SELECTEDVALUE(MD_Calendar[Week])),MD_Calendar[Date])
Return
CALCULATE(SUM(FIN_Key_Stats_Per_Site[ActivePatients]),FILTER(FIN_Key_Stats_Per_Site,[Date]>=a&&[Date]<=b))

Lapsed Patients 52 Weeks = 
var a = MINX(FILTER(ALL(MD_Calendar),[Year]=SELECTEDVALUE(MD_Calendar[Year])-1&&MD_Calendar[Week]=SELECTEDVALUE(MD_Calendar[Week])),MD_Calendar[Date])
var b = MAXX(FILTER(ALL(MD_Calendar),[Year]=SELECTEDVALUE(MD_Calendar[Year])&&MD_Calendar[Week]=SELECTEDVALUE(MD_Calendar[Week])),MD_Calendar[Date])
Return
CALCULATE (
    SUM(FIN_Key_Stats_Per_Site[LapsedPatients]),
    FILTER(FIN_Key_Stats_Per_Site,[Date]>=a&&[Date]<=b))

Left Patients 52 Weeks = 
var a = MINX(FILTER(ALL(MD_Calendar),[Year]=SELECTEDVALUE(MD_Calendar[Year])-1&&MD_Calendar[Week]=SELECTEDVALUE(MD_Calendar[Week])),MD_Calendar[Date])
var b = MAXX(FILTER(ALL(MD_Calendar),[Year]=SELECTEDVALUE(MD_Calendar[Year])&&MD_Calendar[Week]=SELECTEDVALUE(MD_Calendar[Week])),MD_Calendar[Date])
Return
CALCULATE (
    SUM(FIN_Key_Stats_Per_Site[LeftPatients]),
    FILTER(FIN_Key_Stats_Per_Site,[Date]>=a&&[Date]<=b))

TEST Churn % = DIVIDE(([Lapsed Patients 52 Weeks]+[Left Patients 52 Weeks]),[Active Patients Start Period])

All results shown as below:

Best regards,

Dina Ye

[Anonymous]

Hi Dina Ye,

Thanks so much for your reply on this. I think this is really close to what I'm trying to achieve!

Would you possibly be able to visualise this on a bar chart that always shows 52 weeks and explain how that is done? This will almost always straddle 2 different years.

I think your table is showing the last 52 days as opposed to 52 weeks?

I've copied your measures across (which are great by the way!), but I can't seem to get it to visualise in the way that I want. I've removed any link between MD_Calendar and FIN_Key_Stats_Per_Site but still can't get it to work.

Your help is GREATLY appreciated on this!

Cheers,

Aaron

[Anonymous]

@v-diye-msft 

I managed to get this into a chart but I'm not seeing the weeks I was expecting.

Hopefully you can see the image above clearly. I'd expect here, when Year 2019 and Week 30 is selected that I see from 2019 30 back to 2018 30. The chart actually shows from 2018 1, then at week 14 jumps to 30 and continues up to week 52. Year 2019 then begins at week 14 and runs up to week 30.

Any ideas why this would be happening?

Cheers,

Aaron

[v-diye-msft]

Hi @Anonymous ,

Sorry for my late reply. I was calculating the 52weeks rather than 52days. please find the pbix attached here for reference: 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EXE0rK7kTr9ArJnJLeDisgwB8iQPjMlveAFfidR9tMODYw?e=Rgexk9

Best regards,

Dina Ye