Forum Discussion

MarianoV's avatar
MarianoV
Frequent Visitor
1 year ago

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

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!

Need Help

7 Replies

  • MarianoV's avatar
    MarianoV
    Frequent Visitor
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        MarianoV
        Frequent Visitor
        1 year ago

        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?