Filter same periods of different years

[Anonymous]

Hello everybody,

my issue regards the chance to select a period of the year (in format DD/MM) and getting data from that period of every year. I have a date column with data values from year 2016 to 2020, I want a filter where if I select period from 14/02 to 10/03 I get this period for every year I have, so from 14th February to 10th March in 2016, same time for 2017 and so on.

I read some similar discussions but they didn't look me good, either they were not explaining my case rightly or they were unuseful for my aim.

Thank you very much

Nick

Maybe I didn't understand your solution, in this case please point me what I am missing.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce you scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create calculated columns and a  measure as below.

Calculated Column:
MMDD_Date = VALUE(FORMAT('Calendar'[Date],"mmdd"))
MMDD = VALUE(FORMAT('Table'[Date],"mmdd"))

Measure:
Visual Control = 
var _mmdd = SELECTEDVALUE('Table'[MMDD])
var _max = 
CALCULATE(
    MAX('Calendar'[MMDD_Date]),
    ALLSELECTED('Calendar')
)
var _min = 
CALCULATE(
    MIN('Calendar'[MMDD_Date]),
    ALLSELECTED('Calendar')
)
return
IF(
    _mmdd>=_min&&_mmdd<=_max,
    1,0
)

Then you need to put the measure in the visual level filter and use the Month&Day from 'Calendar' to display the result.

Best Regards

Allan

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

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce you scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create calculated columns and a  measure as below.

Calculated Column:
MMDD_Date = VALUE(FORMAT('Calendar'[Date],"mmdd"))
MMDD = VALUE(FORMAT('Table'[Date],"mmdd"))

Measure:
Visual Control = 
var _mmdd = SELECTEDVALUE('Table'[MMDD])
var _max = 
CALCULATE(
    MAX('Calendar'[MMDD_Date]),
    ALLSELECTED('Calendar')
)
var _min = 
CALCULATE(
    MIN('Calendar'[MMDD_Date]),
    ALLSELECTED('Calendar')
)
return
IF(
    _mmdd>=_min&&_mmdd<=_max,
    1,0
)

Then you need to put the measure in the visual level filter and use the Month&Day from 'Calendar' to display the result.

Best Regards

Allan

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

[Anonymous · Accepted solution]

Hi guys and thanks a lot for your hints,

I answer you all together

@amitchandak your way was really good, but I didn't need to create a single measure, it would have had to recreate a new measure for every one I already have and they are a lot, then I was also missing something with ALL and ALLSELECTED functions, because I have a lot of active filters. Anyway your links are great, I'll keep note of them.

@Pragati11 you are right, it depends a lot on what you have to do, what visualizations, how many measures and so on, luckily I found a way later.

@v-alq-msft thank you very much, your hint gave me a brilliant idea. I saw your file but I don't know why it was not working with my case, columns and measures were good but putting [Visual Control] in visual filters was not working. Anyway, I added the condition "[Visual Control] = 1" in every measure and then it begun to work, it's a bit strange I know. Thanks again!

[Pragati11]

HI @Anonymous ,

Can you provide some screenshots on the issue you are facing? It's hard to post a solution this way.

Getting data for same period for all the years may also depend on what kind of visualisations you are trying to do. So it will be nice to have addditional information on this.

Thanks,

Pragati

[amitchandak]

@Anonymous , Use this with a date calendar

Measure =
var _max = format(maxx(allselected('Date'),'Date'[Date]),"MMDD")
var _min = format(minx(allselected('Date'),'Date'[Date]),"MMDD")
return
calculate([Measure],filter(All(DATE), format('Date'[Date],"MMDD") <=_max && format('Date'[Date],"MMDD") >=_min))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.