Filtering line chart with date

[qmestu]

Hi,

I have a fact table and a calendar table, connected with a date field. I also have a slicer for the Quarter. How can i limit the value from the fact table in a line chart, so that it shows results for all the dates before the selection?

Thanks.

[MFelix]

Hi @qmestu ,

One option is to have a slicer with the before option selected this will allow you to select a specific date and then get the value before that date.

If you want to use a single select slicer then you need to have a disconnected table for the slicer so a copy or a partial copy of the calendar table since if you use the same table in the visual and in the slicer the values will be croped to the ones selected on the slicer. Then add a measure similar to this one:

Values before selection =
VAR _MaximumDate = MAX(DisconnectedTable[Date])
VAR _Values = CALCULATE([Measure], Calendar[Date] <= _MaximumDate)
Return
_Values

This should return expected result.

[qmestu]

Hi,

A slicer with the before option is not possible, since my slicer is a combination of year and quarter.

The slicer also filters other visuals on the same page, so it can't be a disconnected table.

Thanks.

[MFelix · Accepted solution]

Hi @qmestu ,

Since the the slicer is a combination of year and quarter then you can use the disconnected table and with sync slicers that will allow you to get the same result and keep the interactions between all the charts.

Can you please share a sample file so I can try and setup an example for you.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[qmestu]

Hi,

I accepted your post as a solution, as it pointed me in the right direction. I created another CalendarTable that is not filtered by anything and used that as the Y axis in my chart.

I then created a measure that compares the date in my slicer to the date in that calendar table, and only shows results where the first is smaller than the second.

Thanks.