LY values not in sync within different visualizations when using two different date slicers

[MarianoV]

Hi everyone! 

When I select a specific date from the graph,  the value for the previous year only shows the value for the first selected date from the previous year slicer.

For context, I have two date slicers (one for current and one for previous years). To calculate for the previous year values, here is the dax formula is: 

Total Sessions PP =

VAR NoOfIntervalDays = CALCULATE ( DATEDIFF ( FIRSTDATE ( 'Dates Current Period'[Date] ), FIRSTDATE ( 'Dates Previous Period'[Date] ), DAY ), ALLSELECTED ( 'Dates Current Period'[Date] ) )

VAR FirstDay = FIRSTDATE ( 'Dates Previous Period'[Date] ) - NoOfIntervalDays

VAR LastDay = LASTDATE ( 'Dates Previous Period'[Date] ) - NoOfIntervalDays

VAR SelectedDatesCP = ALLSELECTED ( 'Dates Current Period'[Date] )

VAR CPDatesToApply = DATESBETWEEN ( 'Dates Current Period'[Date], FirstDay, LastDay )

RETURN

    IF ( SELECTEDVALUE ( 'Dates Current Period'[Date] ) IN CPDatesToApply,

        CALCULATE ( [Total Sessions],

            USERELATIONSHIP ( 'Dates Current Period'[Date], 'Dates Previous Period'[Date] ),

            DATEADD ( 'Dates Current Period'[Date], NoOfIntervalDays, DAY ) ),

        IF ( AND ( SELECTEDVALUE ( 'Dates Current Period'[Date] ) IN SelectedDatesCP,

            NOT SELECTEDVALUE ( 'Dates Current Period'[Date] ) IN CPDatesToApply ),

            BLANK (),

            CALCULATE ( [Total Sessions],

                ALL('Dates Current Period'[Date]),

                USERELATIONSHIP ( 'Dates Current Period'[Date], 'Dates Previous Period'[Date] )

            )

        )

    )

For example, when looked at like in total with no specific date chosen (like in the screenshot), the correct values are shown. But when I select 11/8/2024 in the line graph, the Total Sessions PP will retain 283103, when it should be 256069. Therefore, the vs PP value also changes. 

I want it the values to be dynamic across all visualizations- cards, table, graphs. 

Appreciate all the help, thanks!

[MarianoV]

I think I found the problem: 

VAR NoOfIntervalDays = CALCULATE ( DATEDIFF ( FIRSTDATE ( 'Dates Current Period'[Date] ), FIRSTDATE ( 'Dates Previous Period'[Date] ), DAY ), ALLSELECTED ( 'Dates Current Period'[Date] ) )

This block of code affects the values being displayed when a specific date is selected. When I changed it to -364, which I use to get the same weekday/period last year and the same result for the NoOfIntervalDays VAR, it now shows the correct value when choosing a specific date.

Is there another way to make getting '-364' dynamic aside from using DATEDIFF within calculate?

[Anonymous]

Hi @MarianoV ,

Instead of using DATEDIFF in CALCULATE, you can use the PARALLELPERIOD function to dynamically calculate dates for the same period last year.

VAR CurrentDate = FIRSTDATE ( 'Dates Current Period'[Date] )
VAR PreviousDate = FIRSTDATE ( PARALLELPERIOD ( 'Dates Current Period'[Date], -1, YEAR ) )
VAR NoOfIntervalDays = 
    CALCULATE (
        COUNTROWS (
            FILTER (
                ALLSELECTED ( 'Dates Current Period'[Date] ),
                'Dates Current Period'[Date] >= PreviousDate &&
                'Dates Current Period'[Date] <= CurrentDate
            )
        )
    )

PARALLELPERIOD function (DAX) - DAX | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MarianoV]

thanks @Anonymous ! However, the NoOfIntervalDays seems to not work properly. Ideally, the NoOfIntervalDays should be equal to -364 as it would dynamically get the same day of the week LY and then calculate the total sessions. Would you know another way?

[Andreas-Harlak]

Hi @MarianoV ,

did you try a different formula with sameperiodlastyear ?

[MarianoV]

Hi @Andreas-Harlak ! What do you mean? 

[Andreas-Harlak]

@MarianoV I mean that you can calculate the value of die last year (LV or PP) with 

[MarianoV]

@Andreas-Harlak tried this just now, and apparently it returns the same exact day and not the same day for the week. For example, December 20, 2024 Friday - SAMEPERIODLASTYEAR() returns December 20, 2023 which was on a Wednesday. What I need is to return December 22, 2023 which was the same friday last year.