Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

filter-function to show the dates before and after a variable date

 

I have a problem. I must create 3 visuals. One of them shows all production orders from the choosen day. Another visual shows all open orders from the past days and the third visual shows all started orders from the future days.

 

Have someone a similar problem or an idea to solve this? 

Need Help

4 Replies

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

    Anonymous , Try this with an independent Date table

     

    between =
    var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
    var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
    return
    calculate( sum(Table[value]), filter(Table, Table[Date] <=_max && Table[Date] >=_min))

     

     

    before =
    var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
    var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
    return
    calculate( sum(Table[value]), filter(Table, Table[Date] <=_min))

     

     

    after =
    var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
    var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
    return
    calculate( sum(Table[value]), filter(Table, Table[Date] >=_max))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Thank you for your help. I have used your DAX-code, but it don't works. 

      I swapped SUM with ALLSELECTED. the filter shows the same dates how the daily plan.

       

      I have attached the relationships and the dashboard. 

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Can someone help me to solve this problem?

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    According to your description, I think you can achieve this using the Date relative Slicer in Power BI, then change the visual interaction types of these visuals to “none”, you can follow these documents:

    https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

    https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions#change-the-interaction-behavior

     

    If you still have a problem, you can post your sample pbix file with some test data (without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

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