Showing Values Less than Date chosen

[Anonymous]

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 

 

so the 15th line item is not included because it was canceled on the 18.

So Far I derived a column (showing the date it could show the line till),

  I created a date table to join on the derive date, but how can I show all the lines between the dates picked . I will explain further if needed. Thanks

[Anonymous]

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]

Attached screenshot.

[Anonymous]

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]

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]

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...

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]

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]

Hi This was the work around I used and it worked, but not clean though as stated by the user as well

https://community.powerbi.com/t5/Desktop/Cannot-use-measure-in-slicer/td-p/166909

[Anonymous]

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]

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

[Anonymous]

Hi redsilk - you should be able to do add a measure filter on the fitler pane - but it needs to be a VISUAL level filter, not page or report level. I think they don't allow filtering measures at the page and report levels because there isn't enough context for figuring out what the metric value should be.

Hope this helps!

Scott