DAX query for comparison based on date range

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.