Forum Discussion

milessegni's avatar
milessegni
Frequent Visitor
5 months ago
Solved

Allow user to select a date range and be able to reference the date range in DAX later

I am making a tool for helping my user compare changes in custom time periods. Period A has a date range filter with some tables and Period B has a date range filter with some tables. I'd like to h...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    Create:

    • your normal Calendar table (connected to the fact table)

    • a Period A Date table (disconnected)

    • a Period B Date table (disconnected)

    Use the disconnected tables in two separate slicers. Then your measures can read the selected ranges and apply them with CALCULATE().

     

    1) Base measure

    Sales = SUM ( FactSales[Sales] )

     

    2) Period A measure

    Sales Period A =
VAR StartA = MIN ( 'Period A Date'[Date] )
VAR EndA   = MAX ( 'Period A Date'[Date] )
RETURN
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] >= StartA
                && 'Calendar'[Date] <= EndA
        )
    )

     

    3) Period B measure

    Sales Period B =
VAR StartB = MIN ( 'Period B Date'[Date] )
VAR EndB   = MAX ( 'Period B Date'[Date] )
RETURN
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] >= StartB
                && 'Calendar'[Date] <= EndB
        )
    )

     

    4) Difference

    Sales Diff = [Sales Period B] - [Sales Period A]