Date filter month and day

[pbiandt]

Is there a way to filter just the month and day (eg. Jan 1 - Mar 23 or Feb 15-Mar 15). I have data from past 10 years and would like to look at specific date ranges within the year for each year. So if one selects Mar 1-Mar 15 say, the data for each year will display the values between those dates.

Also, I'm looking for a visualization that would help me in displaying the average of a value over the last 10 years and whether the current year value is higher or lower (indicated with an arrow or color)? Any suggestions?

Thanks!

[pbiandt]

Thanks @amitchandak and @Greg_Deckler for your help.

I've tried both your syntax but I'm still not getting the desired result.

My data consists of a table with ID and Birth Date. I have a date table linked to the birth date.

I would like a table that displays the distinct count of ID by Birth Year filtered by a date slicer such that only the month and day are considered in the date slicer. For example, if the slicer was between Feb 15-Mar 15, then the table would display the distinct count of ID for birth dates between those dates for each year.

@amitchandakIn your solution, I replaced the sum function with the distinctcount function but the filtering doesn't seem to be working.

Thanks @V-lianl-msft for your solution. However, your solution only works if the dates were within a particular month. It wouldn't work if the dates were between months (eg. Feb 15-Mar 15).

[V-lianl-msft · Accepted solution]

Hi @pbiandt ,

Sorry, I didn't fully understand you before.

I created a calendar table and used it as a slicer, then recreated the measure to calculate the sum.

sum_value = CALCULATE(SUM('data'[Amount]),FILTER(data,data[month_day]in VALUES(('date'[month_day]))))

The sample pbix has been updated

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

[V-lianl-msft]

Hi @pbiandt ,

Is this problem sloved?
If not, please let me know.

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

[Anonymous]

Sorry @V-lianl-msft and @Greg_Deckler I don't understand if the post has been solved.

I downloaded @V-lianl-msft file but the filter still depends on year, while @pbiandt looked to filter all of his years for the time period selected. This is what I need to do, meaning I have some dates in many years (2016 to 2020) and need to filter them based just on day and month, so if I choose from January, 1st to March, 19th it selects this period from all the years I have.

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

Thank you very much

Nick 

[amitchandak]

@pbiandt 

Try with all

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 ))

[V-lianl-msft]

Hi @pbiandt ,

1.You can format year, month, day, and then use them as slicers.

2.First, create a measure to calculate the average of the past few years

AVG_all_YEAR = 
VAR SUM_VALUE = CALCULATE(SUM(Sheet8[value]),ALLSELECTED(Sheet8))
VAR YEARS = CALCULATE(DISTINCTCOUNT(Sheet8[YEAR]),ALLSELECTED(Sheet8))
RETURN DIVIDE(SUM_VALUE,YEARS)

Create another measure as conditional formatting.

You can then use icon to represent the trend of each year relative to the total average.

Here is the sample pbix.

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

[amitchandak]

Try like with date calendar

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

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

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/

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Greg_Deckler]

VAR __Max = MAX('Date'[Date])

VAR __Min = MIN('Date'[Date])

VAR __Table =

  UNION(

    FILTER(ALL('Table'),MONTH([Date]) > MONTH(__Min) && MONTH([Date]) < MONTH(__Max)),

    FILTER(ALL('Table'),MONTH([Date]) = MONTH(__Min) && DAY([Date]) >= DAY(__Min)),

    FILTER(ALL('Table'),MONTH([Date]) = MONTH(__Max) && DAY([Date]) <= DAY(__Max))

  )

RETURN

<some calculation across __Table>