Dynamic comparison of time periods

[M_Wavemaker]

Hello,

I am trying to show the data for the same week of the previous year (which I have managed), however I would like to be able filter this by Country. Currently, if I select a country using a slicer, it does not dynamically change my 'Sessions (LY)'. My formula for this is:

Sessions (LY) = 
    VAR CurrentWeek = SELECTEDVALUE(LandingPagedata[WeekNo])
    VAR CurrentYear = SELECTEDVALUE(LandingPagedata[Year])

    RETURN
    CALCULATE( SUM(LandingPagedata[Sessions]),
        FILTER( ALL(LandingPagedata),
            LandingPagedata[WeekNo] = CurrentWeek &&
            LandingPagedata[Year] = CurrentYear -1 ))

Can someone please advise how I make this dynamic for Country? Sorry - I've tried everything I know, I'm very limited in DAX knowledge.

Thanks

M

[v-lili6-msft · Accepted solution]

HI, @M_Wavemaker 

Sessions (LY) = 
    VAR CurrentWeek = SELECTEDVALUE(LandingPagedata[WeekNo])
    VAR CurrentYear = SELECTEDVALUE(LandingPagedata[Year])

    RETURN
    CALCULATE( SUM(LandingPagedata[Sessions]),
        FILTER( ALL(LandingPagedata),
            LandingPagedata[WeekNo] = CurrentWeek &&
            LandingPagedata[Year] = CurrentYear -1 ))

For ALL(LandingPagedata), this returns all the rows in a whole table,  ignoring any filters that might have been applied

So just adjust it as below:

New Sessions (LY) 1 = 
    VAR CurrentWeek = SELECTEDVALUE(LandingPagedata[WeekNo])
    VAR CurrentYear = SELECTEDVALUE(LandingPagedata[Year])

    RETURN
    CALCULATE( SUM(LandingPagedata[Sessions]),
        FILTER( ALL(LandingPagedata[WeekNo],LandingPagedata[Year]),
            LandingPagedata[WeekNo] = CurrentWeek &&
            LandingPagedata[Year] = CurrentYear -1 ))

New Sessions (LY) 2 = 
    VAR CurrentWeek = SELECTEDVALUE(LandingPagedata[WeekNo])
    VAR CurrentYear = SELECTEDVALUE(LandingPagedata[Year])

    RETURN
    CALCULATE( SUM(LandingPagedata[Sessions]),
        FILTER( ALLEXCEPT(LandingPagedata,LandingPagedata[Country]),
            LandingPagedata[WeekNo] = CurrentWeek &&
            LandingPagedata[Year] = CurrentYear -1 ))

Then it will work well.

Best Regards,

Lin

[amitchandak]

Change this

ALL(LandingPagedata)

Try to restrict it to week and year as per need

Sessions (LY) = 
    VAR CurrentWeek = SELECTEDVALUE(LandingPagedata[WeekNo])
    VAR CurrentYear = SELECTEDVALUE(LandingPagedata[Year])

    RETURN
    CALCULATE( SUM(LandingPagedata[Sessions]),
        FILTER( ALL(LandingPagedata[WeekNo]),
            LandingPagedata[WeekNo] = CurrentWeek &&
            LandingPagedata[Year] = CurrentYear -1 ))

Or

Sessions (LY) = 
    VAR CurrentWeek = SELECTEDVALUE(LandingPagedata[WeekNo])
    VAR CurrentYear = SELECTEDVALUE(LandingPagedata[Year])

    RETURN
    CALCULATE( SUM(LandingPagedata[Sessions]),all(LandingPagedata[WeekNo]),all(LandingPagedata[Year]),
        FILTER( LandingPagedata,
            LandingPagedata[WeekNo] = CurrentWeek &&
            LandingPagedata[Year] = CurrentYear -1 ))