Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help i displaying the data with filter and without filter.

Data Set:

Document Date Data
5/5/2020 1
5/6/2020 3
5/23/2020 5
6/1/2020 3
6/2/2020 5
6/5/2020 2
7/1/2022 5
7/3/2020 6

Created a Cal date from table from (2010,1,1 to 2030,12,31)

Joined with date and document date.

In the report section i have taken the Date as a filter and when i select the date range 5/1/2020 to 6/5/2020, i want to display two set of data one is

Document Date Data
5/5/2020 1
5/6/2020 3
5/23/2020 5
6/1/2020 3
6/2/2020 5
6/5/2020 2


second i want to displaythis

7/1/2022 5
7/3/2020 6

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    You can delete the realationship between calendar date and document date, create a control measure like this and set its value as 1 in the visual filter to achieve this:

    Visual control = 
VAR _max =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Document Date] ) > _max, 1, 0 )

     

    Sample file is attached that hopes to help you, please check and try it: Need help i displaying the data with filter and without filter.pbix 

     

    Best Regards,
    Yingjie Li

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

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    To do this, you typically need a disconnected table.  Here is one approach to get your desired result.

     

    1. Make a calculated table with your documents date with no relationship to your original table - 

    Doc Dates = VALUES(Documents[Document Date])
     
    2. Make your slicer from that new column/table
     
    3.  Make one measure and table with this measure
    Data Sum = CALCULATE(SUM(Documents[Data]), VALUES(Documents[Document Date]), TREATAS(VALUES('Doc Dates'[Document Date]), Documents[Document Date]))
     
    And another measure and table with this measure
    Other Dates Sum = CALCULATE(SUM(Documents[Data]), VALUES(Documents[Document Date]), EXCEPT(VALUES(Documents[Document Date]), TREATAS(VALUES('Doc Dates'[Document Date]), Documents[Document Date])))
     
    And this will be the result
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

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

    Anonymous , not sure what logic of date you wanted for second one , but try like

    measure =
    var _max = eomonth(maxx(allselected(Date),Date[Date]),0)+1
    return
    calculate(sum(Table[Data]), filter(all(Date),Date[Date]>=_max))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your input, I only want to display the future date based on max date of selected filter.

      Example:

      Suppose i have selected in filter (1/7/2011 to 6/12/2020)

      then my desired result should be

       

      7/3/2020

      7/1/2022

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

        You can delete the realationship between calendar date and document date, create a control measure like this and set its value as 1 in the visual filter to achieve this:

        Visual control = 
VAR _max =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Document Date] ) > _max, 1, 0 )

         

        Sample file is attached that hopes to help you, please check and try it: Need help i displaying the data with filter and without filter.pbix 

         

        Best Regards,
        Yingjie Li

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