DATESINPERIOD shows dates beyond the maximum date in the data set

[Kaatiiaa]

Hi 

I have a measure:

mr_average_number_of_orders_per_client_ep_rolling_1year = 
CALCULATE(
    [mr_average_number_of_orders_per_client_ep],
    DATESINPERIOD(
        a_confirm_date[confirm_date].[date],
        MAX( a_confirm_date[confirm_date].[date]),
        -365,
        DAY
    )
)

The maximum confirm_date in my dataset is today (21.01.2023) but on the chart the values of this measure is shown also beyond this maximum date (almost one year forward - the last value shown is for 31.12.2023). Why? (sorry for Polish names of months on the chart on the chart). Could you please help with this? Thank you.

[ValtteriN]

Hi,

Are you using values from calendar table in your visual? Based on the title of your chart it seems you are using Year, Month, Day hierarchy. If this is the case check if your relationships are functioning.

E.g. Here I have a similar measure:

The main difference is that I use calendar table in my dax:

Measure 30 = CALCULATE(MAX('Table (15)'[Date]),DATESINPERIOD('Calendar'[Date],max('Table (15)'[Date]),-365,DAY))


Now If my relationship is broken (in this second example I disabled the relationship) the maximum value displayed is based on my calendar table instead of my data:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Kaatiiaa]

Both in my DAX and in the visualization I use confirm_date from calendar table. The maximum confirm_date both in facts table and in calendar table is 21.01.2023. There is an active relationships between calendar table and facts table.

[ValtteriN · Accepted solution]

Okay, I think I got the issue now.

E.g. Here I created a table with dates from today to one year ago:

• Table 6 = CALENDAR(TODAY()-365,TODAY())
  
  Now my visual has year, month day hierachy and the dax is as follows:
  
  
  Measure 31 = CALCULATE(COUNT('Table 6'[Date]),DATESINPERIOD('Table 6'[Date].[Date],MAX('Table 6'[Date].[Date]),-365,DAY))
  
  We can see the same issue:
  
  

When I remove the .[Date] from the dax the result is as expected:

Measure 31 = CALCULATE(COUNT('Table 6'[Date]),DATESINPERIOD('Table 6'[Date],MAX('Table 6'[Date].[Date]),-365,DAY))

[Kaatiiaa]

Ok, I worked it out but I don't yet understand why it works like this. When I removed  .[Date] only from the MAX part and left it in the DATESINPERIOD part it worked as I expected. Thank you for help

mr_average_number_of_orders_per_client_ep_rolling_1year = 
CALCULATE(
    [mr_average_number_of_orders_per_client_ep],
    DATESINPERIOD(
        a_confirm_date[confirm_date].[date],
        MAX( a_confirm_date[confirm_date]),
        -365,
        DAY
    )
)

[Kaatiiaa]

Thanks for helping. In my opinion however the result after removing .[Date] form the DAX is not as expected. I would expect the first chart you shared to end on todays date as on the screen below - withou the part crossed out (in red). 

[ValtteriN]

Okay, I think I got the issue now.

E.g. Here I created a table with dates from today to one year ago:

• Table 6 = CALENDAR(TODAY()-365,TODAY())
  
  Now my visual has year, month day hierachy and the dax is as follows:
  
  
  Measure 31 = CALCULATE(COUNT('Table 6'[Date]),DATESINPERIOD('Table 6'[Date].[Date],MAX('Table 6'[Date].[Date]),-365,DAY))
  
  We can see the same issue:
  
  

When I remove the .[Date] from the dax the result is as expected:

Measure 31 = CALCULATE(COUNT('Table 6'[Date]),DATESINPERIOD('Table 6'[Date],MAX('Table 6'[Date].[Date]),-365,DAY))