Forum Discussion

nachoroncero's avatar
nachoroncero
Frequent Visitor
4 years ago
Solved

New in Power BI - Using two date filters

Hello,

 

Sorry if y question is too newbie, it is my first time using this tool.

 

I am trying to show the results of a company in a specific month, but also in during a period of time.

In the guage, I would like to see the result for August 2018, and in the line chart, I would like to see all the results from 2017 to August 2018.

One solution is using two slicers, one for the specifict month, and the other to set the period and male it interact only with the line chart. This works, but it is not efficient.

 

I believe there must be a way to capture the month selected in the filter above, and use THAT same value for the second slicer that filters the time period.

How can I do that so I only have to indicate the desired month once?

 

Thank you ver much for your time!

Data Protection
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi nachoroncero , 

    Please refer to my pbix file to see if it helps you.

    Create a measure.

     

    Measure =
VAR _start =
    DATE ( 2017, 1, 1 )
VAR _value =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= _start
                && 'Table'[date] <= SELECTEDVALUE ( 'Table 2'[Date] )
        )
    )
RETURN
    _value

     

     

    Cancel the work of the slicer on the guage, select Format>>Edit interaction.  

    Then choose none.

    Then filter the data on this visual.

     

     

     

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    nachoroncero , Assuming the date table is governing, then we can get min and max month

     

    Meausre =
    var _min = eomonth(minx(allselected('Date'), 'Date'[Date]),0)
    var _max = eomonth(maxx(allselected('Date'), 'Date'[Date]),0)
    return
    CALCULATE(sum('Table'[Qty]), FILTER(('Date'),eomonth('Date'[Date],0) = _max)) - CALCULATE(sum('Table'[Qty]), FILTER(('Date'),eomonth('Date'[Date],0) =_min))

     

     

    or

    How to use two Date/Period slicers

    https://youtu.be/WSeZr_-MiTg

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi nachoroncero , 

    Please refer to my pbix file to see if it helps you.

    Create a measure.

     

    Measure =
VAR _start =
    DATE ( 2017, 1, 1 )
VAR _value =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= _start
                && 'Table'[date] <= SELECTEDVALUE ( 'Table 2'[Date] )
        )
    )
RETURN
    _value

     

     

    Cancel the work of the slicer on the guage, select Format>>Edit interaction.  

    Then choose none.

    Then filter the data on this visual.

     

     

     

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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