How to add StartMonth and EndMonth slicers to a report?

[Anonymous]

I am re-writing an SSRS report in Power BI. The SSRS report has two parameters, StartMonth and EndMonth. Ther report displays data between those two months. The paramters are formatted as Mmm-yyyy. How do I achieve the same thing in Power BI? I can create two slicers, each using the dimDate.DateMonthYear field. But as soon as I select one month in the StartDate slicer, the EndDate slicer changes to display only the same month.

Any ideas?

[Anonymous]

Hi @Anonymous ,

You may try to put the date column into Filters and set the start date and end of it.

Best Regards,

Stephen Tao

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

[danextian]

Hi @Anonymous ,

As @Anonymous has suggested, you may use the between dates of the slicer. Otherwise, you'll have to create two separate and disconnected tables (no relationship to the fact table).  Create calculated tables using this formula

StartDate = 
VAR __START_DATE =
    DATE ( 2019, 1, 1 ) 
VAR __MONTHS =
    GENERATESERIES ( 0, 36, 1 )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS ( __MONTHS, "Date", EOMONTH ( __START_DATE, [Value] ) ),
        "Month and Year", FORMAT ( [Date], "mmm-yy" )
    )


EndDate= 
VAR __START_DATE =
    DATE ( 2019, 1, 1 ) 
VAR __MONTHS =
    GENERATESERIES ( 0, 36, 1 )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS ( __MONTHS, "Date", EOMONTH ( __START_DATE, [Value] ) ),
        "Month and Year", FORMAT ( [Date], "mmm-yy" )
    )

And to aggregate a value based on the selected start and end months from the two separate tables.

MyMeasure =
CALCULATE (
    SUM ( Fact[Column] ),
    DATESBETWEEN ( Fact[Date], MIN ( StartDate[Date] ), MIN ( EndDate[Date] ) )
)
//using MIN or MAX for the end and start dates doesn't matter as there is only one date per month in the disconnected tables

[Anonymous]

Is it possible to combine both start month and end month field into one column, like a date column? If it's possible, then you can just use a date slicer as shown below by using the between option. This way you can choose both the start and end date.