Calculation based on slicer selected values

[Anonymous]

Hi,

I have three slicers and tables in my report: slicer of year, month, day, one table is summary statistics of the selected date, another table is the statistics for day of week of the selected date within past one year.

For example if I select year=2019, month=Mar, day= 10, which is a sunday, I need to select all the sundays between March 10, 2018 and March 10, 2019.

By doing this, first I created a measure:

selected date =
var year = value( IF(HASONEVALUE('Offer Date'[Year]), VALUES('Offer Date'[Year]), year(NOW())))
var month = value(IF(HASONEVALUE('Offer Date'[Month]), VALUES('Offer Date'[CALENDAR_MONTH_NUMBER_IN_YEAR]), MONTH(NOW())))
var day =  value( IF(HASONEVALUE('Offer Date'[Day]), VALUES('Offer Date'[Day]), DAY(NOW())))
return date (year, month, day)
 

and then I compare all my available date with the selected date by creating a column

HC reporting range =
        IF(AND(
            [CALENDAR_DATE]>=[selected date]-365,
        [CALENDAR_DATE]>=[selected date]),
       "Yes", "No")

This column is not functional as Iexpected. I learned online that I shouldn't create a column. Instead, I should create a new table. But I don't know how to do that.

Please help~~~

[sturlaws · Accepted solution]

Hi @Anonymous,

you are right, it is possible to create a calculated column based on a measure, but a calculated column will not change if the measure changes due filtering by slicers.

It is not clear to me what you want to achive, but I created this little report as a simple demo of how filter dates based on slicers.

Cheers,

Sturla