Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Kaatiiaa
Frequent Visitor

DATESINPERIOD shows dates beyond the maximum date in the data set

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.

 

Kaatiiaa_0-1674305256403.png

 

1 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:

    ValtteriN_0-1674309386087.png

     

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

ValtteriN_1-1674309540975.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ValtteriN
Super User
Super User

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:

ValtteriN_0-1674306463474.png

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:
ValtteriN_1-1674306588962.pngValtteriN_2-1674306604711.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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:

    ValtteriN_0-1674309386087.png

     

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

ValtteriN_1-1674309540975.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

 

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

 

Kaatiiaa_0-1674389279629.png

 

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:

    ValtteriN_0-1674309386087.png

     

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

ValtteriN_1-1674309540975.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors