Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Showing Values Less than Date chosen

Hi ,  I have a table in this format    I am trying to choose a date or range (for example when i choose 16-05-2019) the output should be  but if I choose 19-05-19 I will want to see    ...
Interesting Links
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous 

 

You should be able to just create a date slicer visual, drag the date field onto it, and then change it to a date type of "before". This will allow the end user to choose the end date - all dates before it will be included.

 

Hope this helps,

Scott

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Attached screenshot.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Spowell, 

     

    In this example when I  use the before slicer for what happened befored the 19-01-19, it will include the 15-05-19 and  I want to exclude it because it was cancelled on the 18-01-19.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Ah, ok, I don't think I understood. Just to clarify, what you want is for items to show up if their created date is before the selected date on the report AND it hasn't been cancelled before the selected date.

       

      Can you confirm if I have that correctly?

       

      Thanks,

      Scott

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi, 

        Yes you are correct, I used the logic in this forum question

        https://community.powerbi.com/t5/Desktop/Dynamic-column-values-based-on-selected-date-Need-help/td-p/426820

        and I came up with three measures which kind of worked, but I will like this measures to be calculated column so that I can use it as a slicer(or is there a way i can convert a measure to a slicer)

         


        1. Measure 15 = VAR selectedDate = IF (HASONEVALUE('PBI PowerBiDataSet'[cancdate]),MIN('PBI PowerBiDataSet'[cancdate])) RETURN IF( MAX('conformed DimDate'[CalendarDate]) > selectedDate,"FALSE","TRUE")

         

        2. Measure 16 = VAR selectedDate = IF (HASONEVALUE('PBI PowerBiDataSet'[Opportunity Close Date]),MIN('PBI PowerBiDataSet'[Opportunity Close Date])) RETURN IF( MAX('conformed DimDate'[CalendarDate]) < selectedDate,"FALSE","TRUE")


        3.Measure 17 = IF([Measure 15] && [Measure 16] = "TRUE" ,"PICK","IGNORE")

         

        Column Context

        PBI PowerBiDataSet'[cancdate] this column holds the cancelled date if any or it will be like 9999-01-01

        conformed DimDate'[CalendarDate] this is the column on the date

        PBI PowerBiDataSet'[Opportunity Close Date]