Forum Discussion

tomas12344's avatar
tomas12344
Helper III
1 year ago
Solved

Time intelligence function working for leap year or not

I found many post and articles on web about that time intelligence functions are not woking for leap year calculations. Can someone confrim, that this issue is still present?

 

I had creted a small example, where the calendar table contains many day, including 28.5.2025 and 29.2.2024. Same for fact table. 
Then I run something like this, but only for 28.2.2025

EVALUATE
VAR StartDate = DATE ( 2008, 07, 25 )
VAR EndDate =   DATE ( 2008, 07, 31 )
RETURN
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        Date'[Date] = Date(2025,02,28)
    )
ORDER BY [Date]

this returned the date 28.2.2024, so the exact date as selected was moved backwards 1 year.

 

But when I used the Sameperiodlastyear function in measure calculation, it returned correct values, like
Calculate(sum(), sameperiodlastyear('date'[date])
(this measure used for MTD and aslo YTD calculation for previous year, that means selecte in calendar table 28.2.2025, and needed to move back to 29.2.2024)

Model is just simple, calendar table and fact table, and between calendar and fast is relation on date column. Fact table contains only monthly snapshots, calendar contains all days

 

 

Need Help
  • Sergii24's avatar
    Sergii24
    1 year ago

    Hi tomas12344, the best solution is to test it out 🙂

     

    As you can see from the example below, SAMEPERIODLASTYEAR() considers leap year:

     

    I always suggest to have a tehcnical measures that are showing you what period is actually selected (which might be different from what one expects 😉 )

     

    Here is a code of the measure for this test:

     

    Same Period Last Year = 
VAR _SamePeriodLastYear = SAMEPERIODLASTYEAR( c_Calendar[Date] )
VAR _MinDate = MINX( _SamePeriodLastYear, [Date] )
VAR _MaxDate = MAXX( _SamePeriodLastYear, [Date] )
VAR _Result = 
    IF(
        _MaxDate <> _MinDate,
        "Currently Selected Same Period Last Year: " & _MinDate & " - " & _MaxDate,
        "Currently Selected Same Period Last Year: " & _MinDate
    )
    
RETURN
    _Result

     

     

     

    Good luck with your task!

4 Replies

  • Sergii24's avatar
    Sergii24
    Super User
    1 year ago

    Hi tomas12344, the best solution is to test it out 🙂

     

    As you can see from the example below, SAMEPERIODLASTYEAR() considers leap year:

     

    I always suggest to have a tehcnical measures that are showing you what period is actually selected (which might be different from what one expects 😉 )

     

    Here is a code of the measure for this test:

     

    Same Period Last Year = 
VAR _SamePeriodLastYear = SAMEPERIODLASTYEAR( c_Calendar[Date] )
VAR _MinDate = MINX( _SamePeriodLastYear, [Date] )
VAR _MaxDate = MAXX( _SamePeriodLastYear, [Date] )
VAR _Result = 
    IF(
        _MaxDate <> _MinDate,
        "Currently Selected Same Period Last Year: " & _MinDate & " - " & _MaxDate,
        "Currently Selected Same Period Last Year: " & _MinDate
    )
    
RETURN
    _Result

     

     

     

    Good luck with your task!

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi tomas12344,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you Sergii24 for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Community Support
      1 year ago

      Hi @tomas12344,

       

      We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.

      If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

       

      Thanks and regards,

      Anjan Kumar Chippa

      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi @tomas12344,

         

        As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
        If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

         

        Thanks and regards,

        Anjan Kumar Chippa