Show dates less than the selected slicer month

[PBIfanatic]

Hi,

I have a line chart using year and month on the x axis and a measure on the y axis.

Is there a way we can use a slicer and show the data in the chart for months only prior to the selection.

Lets say we select March 20, the chart should show data from March 20 backwards.. even if we have data till July 20.

Is this possible?

[v-alq-msft · Accepted solution]

Hi, @PBIfanatic 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table) :

Calendar = CALENDARAUTO()

You may create calculated columns and measures as below.

Calculated column:

Calendar Year-Month = YEAR('Calendar'[Date])*100+MONTH('Calendar'[Date])
Table Year-Month = YEAR('Table'[Date])*100+MONTH('Table'[Date])

Measure:

Visual Control = 
IF(
    SELECTEDVALUE('Table'[Table Year-Month])<SELECTEDVALUE('Calendar'[Calendar Year-Month]),
    1,0
)

Finally you may put the measure in the visual level filter and use the date column from ‘Calendar’ table to filter the result.

Best Regards

Allan

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

[PBIfanatic]

@v-alq-msft  thats brilliant!!

Exactly what I was after. Thanks so much.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[AllisonKennedy]

@PBIfanatic
You could change the date slicer to a BEFORE option or a RELATIVE slicer: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

You could also really complicate things by updating your visuals to use measures that take the selected date in the slicer and return data for all dates prior to that using Time Intelligence DAX, but that would not be my recommended approach.

[pranit828]

Hi @PBIfanatic 

A date slicer when used will always show data for the selected date and nothing pre or post the selected date.

I would use the input parameter instead in your case. below is the link to use Input parameter. use the below linki to get more details on it.

https://www.antaresanalytics.net/post/2018/09/11/power-bi-creating-user-input-variables