How to make lines for line chart with different date range start at the same point?

[milodinosaur]

Hi all, I have been troubling over this for a really long time and would appreciate some help from the community. 

I have 2 date slicers to let users select customised periods to compare. I am doing a simple count of the values and have created a duplicated date table joined by an inactive relationship. However, it is not meaningful comparison if they do not both start at the start of the graph.

I tried many ways such as finding the min and max selected dates to find the difference between them then using DATEADD to shift it. But its not working for me. 

This is my current DAX Code:

ComparisonLine =

VAR SelectedStartDate = MIN('Date Table'[Date])

VAR SelectedEndDate = MAX('Date Table'[Date])

RETURN

    CALCULATE (

        COUNT('Fact'[Case Number]),

        FILTER (

            ALL('Date Table'[Date]),

            'Date Table'[Date] >= SelectedStartDate && 'Date Table'[Date] <= SelectedEndDate

        ),

        USERELATIONSHIP ( 'Date Table'[Date], 'Date Table (2)'[Date] )

    )

This is my desired outcome:

Not sure if the x-axis of my chart plays a part. It is the Month Year, which is done by concatenating Month name and Year together. the Date column in the date tables is in the DD/MM/YYYY format. While the date format in my fact table is Monday, 25 January 2021. 

Would appreciate any help given! Thank you~

[MFelix]

Hi @milodinosaur ,

Not sure if I understood your requirements, you present values from two different lines but you only have date axis, looking at the image you present you want to have that the values from the second line that goes from march to august to be presented on the beginnig of the chart so starting on january?

I believe that using this option will confuse your users since they will look at the axis and read january when in fact the information is for March.

Does it make sense to keep the dates in x-axis?

One option is to get an alias for each of the days in your selection so instead of having the dates you would have day 1, day 2 and so on. Does this make sense to you?

[milodinosaur]

Hi Felix!

Yes, you understood my requirements perfectly! I hear your concerns, but I have thought of using a report page tooltip to minimise confusion. In fact, I was hoping to achieve something similar to the comparison function in Google Analytics.

I've seen some people attempt it and have tried to follow their method but I can't seem to get it to work, do you have any ideas how I can go about doing this? 

[MFelix · Accepted solution]

Hi @milodinosaur ,

Sorry for the late response.

What you can do is the following is to add the following measures:

Total Days Difference =
DATEDIFF (
    CALCULATE ( MIN ( 'Date'[Data] ), ALLSELECTED ( 'Date'[Data] ) ),
    MIN ( CalendarFilter[Data] ),
    DAY
)

Total Value Difference =
IF (
    CALCULATE (
        MAX ( 'Date'[Data] ),
        DATEADD ( 'Date'[Data], [Total Days Difference], DAY )
    )
        <= MAX ( CalendarFilter[Data] ),
    CALCULATE (
        SUM ( Orders[Quantity] ),
        DATEADD ( 'Date'[Data], [Total Days Difference], DAY )
    )
)

The first one calculates the difference in days from the first slicer (calendar table) to the second one ( second period you want to filter).

Second measure makes the calculation going back the number of days from the first date to the second and referencing it to the period selected.