Slicer for Custom Business Year and Month Range

[DRusso]

Hello,

I'd like to create a slicer that allows users to filter by the current active business year. The custom range would always be the 1st day of the selected month and year, rolling back to the 2nd day of the same month in the previous year. Example below:

With the data filtered as above, the dates displayed should be:

January 2, 2022 - January 1, 2023

Another example - If 2020 and May were selected in the slicers, the dates displayed should be:

May 2, 2019 - May 1, 2020

I already have a Dates table set-up using CALENDARAUTO() with a relationship to my sales table. 

Thanks a bunch!

DR

[v-binbinyu-msft · Accepted solution]

Hi @DRusso ,

Please try below steps:

1. below is my test table

Table:

Table2:

Sales:

2. create a measure with below dax formula

Measure =
VAR _year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _month =
    SELECTEDVALUE ( 'Table 2'[Month] )
VAR cur_date =
    SELECTEDVALUE ( Sales[Date] )
VAR tmp =
    CALENDAR ( DATE ( _year - 1, _month, 2 ), DATE ( _year, _month, 1 ) )
RETURN
    SWITCH (
        TRUE (),
        OR (
            NOT ( HASONEVALUE ( 'Table'[Year] ) ),
            NOT ( HASONEVALUE ( 'Table 2'[Month] ) )
        ), 1,
        cur_date IN tmp, 1
    )

3. add slicer visual with Table and Table2 field, add a Table visual with Sales field, add measure to table visual filter pane and set

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DRusso]

Hi @v-binbinyu-msft,

Sorry about the late reply, was away for a couple weeks and just came back to this. This worked great! Is there anyway to allow for the data to be displayed by year only and compared to the previous year?

I'm able to achieve a comparison the the previous year using the SAMEPERIODLASTYEAR measure, but only if I use the the full date column in the table.  

I'd like to be able to display the data by year only, using the custom range filter you built in a table and a bar chart. Simple example below but these are ignoring the filters. 

Best,

DR