Date Range Slicer (No Year)

[ahhines]

Hello,

I have data from 1/1/2018-3/31/2020 and I want to see if I can show the sum of my values from 2/15-3/15 for all years 2018, 2019, and 2020.

So ideally I want to be able to show any range of time with the year as a legend. Has anyone tried doing this before?

[amitchandak · Accepted solution]

@ahhines 

Assume you have date table and you have year as one of the column in that and you selected data from 15-feb to 15-march for any year

Have formula like this

measure =
var _max = format(maxx('Date','Date'[Date]),"MMDD")
var _min = format(Minx('Date','Date'[Date]),"MMDD")

return
CALCULATE(SUM(Data[Amount]), FILTER(all('Date'), format('Date'[Date],"MMDD") >=_min && format('Date'[Date],"MMDD") <=_max ))

Use year as a legend.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[ahhines]

Thanks! This is a great start. This is giving me a result three times the actual amount though. 

[Pragati11]

Hi @ahhines ,

Your requirement is not clear:

I have data from 1/1/2018-3/31/2020 and I want to see if I can show the sum of my values from 2/15-3/15 for all years 2018, 2019, and 2020.

What does the highlighted part mean here? Also provide some screenshots on your data and data model and what output is expected?

Thanks,

Pragati

[ahhines]

Sorry, I'm not sure why that was highlighted. I want to be able to select any date range, say 2/15-3/15, for years 2018, 2019, and 2020. Using a regular date filter from 2/15/2018-3/15/2020 would give me all days in this range and I do not want that. I want to just see the 2/15-3/15 period for each year.