Forum Discussion

milessegni's avatar
milessegni
Frequent Visitor
6 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 have a third column that shows the change in Period B vs Period A.

 

How do I allow a user to select/filter two data ranges, then have those dates saved somehow (variable?)so that I can reference it in DAX later so I could, for example, Sales_Diff = Period_A_Sales - Period_B_Sales  


Note: in Tableau this was done with the Parameter feature, not filters. Parameters could be referenced by the entire workbook once selected and had their own field name for use in formulas.

 

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    6 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]

5 Replies

  • Zanqueta's avatar
    Zanqueta
    Icon for Super User rankSuper User
    6 months ago

    Hi  milessegni

    Power BI does not store slicer selections as reusable variables. This differs from Tableau, where Parameters act as global values that calculations can reference anywhere.


    In Power BI, what can work reliably is to replace your date‑range slicers with user‑selected parameters stored in a disconnected table. These tables allow users to pick a start and end date for Period A and Period B, and those selections can then be referenced directly inside DAX measures.
    This pattern is commonly known as the “Disconnected Parameter Table” approach and is the recommended method for custom time‑period comparisons.

     

    You must try:

    • Use disconnected date tables to act as user‑selectable “parameters”.
    • Capture selected start/end dates with MIN()/MAX() measures.
    • Build measures filtering the main date table using those captured parameter values.
    • Calculate the comparison measure (Period B – Period A) using standard DAX.

       

     

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    6 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]
  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    6 months ago

    milessegni 

    Create two identical date tables - Date_A and Date_B (duplicate your date table in PQ).

    Add slicers: one for Date_A range, one for Date_B range.

    Period A Sales:

    VAR _minA = MIN(Date_A[Date])
    VAR _maxA = MAX(Date_A[Date])
    RETURN CALCULATE([Sales], Date[Date] >= _minA, Date[Date] <= _maxA)

    Period B Sales: Same logic with Date_B.

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

     

    Disconnected slicers let users pick independent ranges.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 months ago

    Hi milessegni,

    I would also take a moment to thank cengizhanarslan  , Kedar_Pande, Zanqueta  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

    Regards,
    Community Support Team.