DAX query for comparison based on date range

[JayashreeG1502]

Hi,

I have a requirement,

If the date period is selected for example May 2 to May 5 2022 then the interval for these is 3 days, the data displayed in the table,

• Weekwise should be the last 3 weeks where the interval (3 days) is subtracted from the range selected i.e., April 28 to 25, April 21 to 18, April 14 to 11.
• Monthwise should be the last 3 months same range selected i.e., April 2 to 5, March 2 to 5, Feb 2 to 5.
• Yearwise should be the last 2 years same range selected i.e., May 2 to 5 2021, May 2 to 5 2020.

[JayashreeG1502]

Hi @Icey,

Thanks for your help. It's working.

[Icey · Accepted solution]

Hi @JayashreeG1502 ,

Try this:

Steps:

1. Create a LabelTable via "Enter Data" and sort columns.

2. Create a Date table and DO NOT create relationships between it and your fact table.

Date = CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2022, 12, 31 ) )

3. Create measures.

Date Measure = 
SWITCH (
    SELECTEDVALUE ( LabelTable[Label] ),
    "Selected Date",
        MIN ( 'Date'[Date] ) & " - "
            & MAX ( 'Date'[Date] ),
    "Previous Week",
        MIN ( 'Date'[Date] ) - 7 & " - "
            & MAX ( 'Date'[Date] ) - 7,
    "Week 2",
        MIN ( 'Date'[Date] ) - 7 * 2 & " - "
            & MAX ( 'Date'[Date] ) - 7 * 2,
    "Week 3",
        MIN ( 'Date'[Date] ) - 7 * 3 & " - "
            & MAX ( 'Date'[Date] ) - 7 * 3,
    "Previous Mon",
        MINX ( DATEADD ( 'Date'[Date], -1, MONTH ), [Date] ) & " - "
            & MAXX ( DATEADD ( 'Date'[Date], -1, MONTH ), [Date] ),
    "Mon 2",
        MINX ( DATEADD ( 'Date'[Date], -2, MONTH ), [Date] ) & " - "
            & MAXX ( DATEADD ( 'Date'[Date], -2, MONTH ), [Date] ),
    "Mon 3",
        MINX ( DATEADD ( 'Date'[Date], -3, MONTH ), [Date] ) & " - "
            & MAXX ( DATEADD ( 'Date'[Date], -3, MONTH ), [Date] ),
    "Previous Year",
        MINX ( DATEADD ( 'Date'[Date], -1, YEAR ), [Date] ) & " - "
            & MAXX ( DATEADD ( 'Date'[Date], -1, YEAR ), [Date] ),
    "Year 2",
        MINX ( DATEADD ( 'Date'[Date], -2, YEAR ), [Date] ) & " - "
            & MAXX ( DATEADD ( 'Date'[Date], -2, YEAR ), [Date] )
)

Value Measure = 
SWITCH (
    SELECTEDVALUE ( LabelTable[Label] ),
    "Selected Date",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FactTable[Date] IN VALUES ( 'Date'[Date] )
        ),
    "Previous Week",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -7 * 1, DAY )
            )
        ),
    "Week 2",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -7 * 2, DAY )
            )
        ),
    "Week 3",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -7 * 3, DAY )
            )
        ),
    "Previous Mon",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -1, MONTH )
            )
        ),
    "Mon 2",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -2, MONTH )
            )
        ),
    "Mon 3",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -3, MONTH )
            )
        ),
    "Previous Year",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -1, YEAR )
            )
        ),
    "Year 2",
        CALCULATE (
            SUM ( FactTable[RandValue] ),
            FILTER (
                FactTable,
                FactTable[Date]
                    IN DATEADD ( 'Date'[Date], -2, YEAR )
            )
        )
)

4. Create a matrix visual and switch values to rows.

For more details, check the attachment.

Best Regards,

Icey

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