Dates - Years Slicers

Power BI
1

I know I am over thinking this…

If I have a dates table that has dates from 2010-2020.

I have a SALES table with ORDER DATES from 2015-2020.

I want to have a SLICER on the page with ONLY the YEARS that exist in the SALES table.

Thanks in advance.

2

You can try editing the interactions, but not sure that would work. You could also add a column to your date table ( 1 for true, 0 for false) if the date in the date table falls in between the firstdate and last date of the sales table. Id use 1 and 0 so you can add up days easily if that is ever a requirement. You can add that column as a filter on the slicer and set to 1, so that only those dates would show.

3

I was thinking that some combination of VALUES and RELATED would work but not sure.

4

You can do this in Power Query or as a Calculated Column. I prefer to do this type of thing in Power Query, but here’s both options:

  1. Power Query
    Find the First and Last Date of Sales. Reference Sales and can use List.Min and List.Max
    image

Then you can add a column to your Calendar Table:

if [Date] >= MinOfSales and [Date] <= MaxOfSales then 1 else 0

In this example the calendar table goes from 2019 to 2020, while sales is smaller:

image
image792×1037 53.7 KB

the dax route would be:

Date Between Sales Calc Column = 
VAR _LastDate=
CALCULATE( LASTDATE( Sales[Date] ), ALL( 'Calendar'))

VAR _FirstDate=
CALCULATE( FIRSTDATE( Sales[Date] ), ALL( 'Calendar'))

Var _DateBetween=
IF(
    'Calendar'[Date]>=_FirstDate && 'Calendar'[Date]<=_LastDate
    ,1
    ,0
)

RETURN
_DateBetween

then you can use that column as a filter on your slicer, measures, etc
Calendar Dates with Sales.pbix (48.8 KB)

1 Like
5

Hi @mbraun, we’ve noticed that no response has been received from you since the 2nd of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!