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, 

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]

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Well, you won't be able to do this as a calculated column - because you're using dynamic slicers, and calculated columns are calculated before you even get in to the front end of Power BI.

 

I actually ran into an exactly similar situation at work today - someone wants to filter / slice based on a calculation that doesn't work for "pre calculating". I'll update this if we find a nice way to make this work.

 

Thanks,

Scott

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Redsilk - I've tested, and I'm not having issues using measures in filters (vs. slicers), this seems to work fine. We're in the process of moving away from slicers altogether - with the new filter pane experience, filters are much nicer.

       

      Can you use a filter instead?

       

      Thanks,

      Scott

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi sp42, 

         

        I just tried it but the filter pane will not accecpt the measure column. I will look further into it, but at them moment we are managing with the fix from the link I sent above