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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Kaatiiaa
Helper I
Helper I

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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