DAX and leap year, date range

[JK-1]

Been researching this a while. Its not the usual Year on Year, and it not finding a corresponding 29th Feb but the period of a date range calculated.

I am trying to bring in a count to then subtract that from the total. I've found something useful via Colin Maitland from 4 years ago but having problems finalising it.

Code is:  Its the removefilters & datesbetween having difficulty with. Even if I resolved that I'm not sure it will provide the solution so was looking for any other ideas.  Thanking you

calc = VAR _MAX_DATE = MAX ( 'Date'[Date], )

VAR _DAYS_IN_YEAR =

IF (

NOT ISBLANK ( _MAX_DATE ),

SWITCH (

TRUE(),

// Is Max Date Year a Leap Year?

DATEDIFF ( DATE ( YEAR ( _MAX_DATE ), 02, 28 ), DATE ( YEAR ( _MAX_DATE ), 03, 01 ), DAY ) = 2,

IF (

_MAX_DATE >= DATE ( YEAR ( _MAX_DATE ), 02, 29 ), // NOTE: Compare with 29/02 of the highest selected Year here.

366, // Include 365 Days plus 29th February in the highest selected Year.

365

),

// Is Max Date Previous Year a Leap Year?

DATEDIFF ( DATE ( YEAR ( _MAX_DATE ) - 1, 02, 28 ), DATE ( YEAR ( _MAX_DATE ) - 1, 03, 01 ), DAY ) = 2,

IF (

_MAX_DATE <= DATE ( YEAR ( _MAX_DATE ), 02, 28 ), // NOTE: Compare with 28/02 of the highest selected Year here.

366, // Include 365 Days plus 29th February in the previous Year to the highest selected Year.

365

),

// Not a Leap Year

365

)

)

VAR _MIN_DATE =

IF (

NOT ISBLANK ( _MAX_DATE ),

( _MAX_DATE - _DAYS_IN_YEAR ) + 1 // Adjust by one Day to ensure that same date as Max Date from previous Year is not included.

)

VAR _RESULT =

IF (

NOT ISBLANK ( _MAX_DATE ),

CALCULATE (

SELECTEDMEASURE(),

REMOVEFILTERS ( 'Date Range' ),

DATESBETWEEN (

'Date'[Date],

_MIN_DATE,

_MAX_DATE

)

)

)

RETURN

_RESULT

//In this example, if there was no need to ensure that 365 days plus the 29th of February needed to be included in this calculation, then the VAR _DAYS_IN_YEAR = part of the formula could simply be changed to VAR _DAYS_IN_YEAR = 365.

[v-tsaipranay]

Hi @JK-1 ,

Thank you for reaching out to the Microsoft fabric community forum.

Thanks for sharing the details. I reproduced your scenario with your sample ranges and created a solution that calculates the day difference strictly on a 365-day basis. The logic is:

• Count the total days between From and To (inclusive).
• Detect if any Feb 29 dates fall inside the range.
• Subtract those leap days to arrive at the corrected total.

I’ve attached a PBIX showing this implemented with calculated columns (DaysBetween_Inclusive, LeapDays_InRange, and Corrected_Days). With this approach your ranges give the expected results.

Hope this helps. Please reach out for further assistance.

Thank you.

[JK-1 · Accepted solution]

That is amazing. I shall give it some testing, though it already looks perfect. Thank you so much for your help, time and guidance

[FBergamaschi]

Please provide more insights:

What are you trying to accomplish? What are the issues you found? Is the code the one of a calculated column?

Thanks

[JK-1]

From, To, days between and corrected in the screenshot are the columns used in a calc. The count column is a manual comment. The code I haven't achieved a result yet / more below

I couldn’t get the VAR code to work around the min max dates.

I have a large batch of From/To but need each line to calculate on a 365 day basis but where it spans a leap year I don’t need the extra day accounted for. I am likely to have a large array of From dates so not sure having a different calendar table will help?

So, accomplish- calc on a 365 day basis from the ranges per row From/To

Issues found: unable to calculate an identifier to give a value to deduct from the 366 being calculated where applicable / nearest solution was the code but got stuck around min, max dates between and date range section of that code sourced

Code: not yet been able to achieve success as I am missing a part of the logic in one of the steps to complete. I don’t know if that will give the count or the actual 365 day basis ignoring leap year.

Kind regards

[FBergamaschi]

I still have not a clear idea of what you are looking for

a column, a measure?

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here