Forum Discussion

stxreporting's avatar
stxreporting
Frequent Visitor
4 years ago

Time intelligence not returning correct value

Hi,

 

I'm using a dateadd function that calculates the previous period's value using a separate date table.

 

It was brought to my attention by a user that the values for 2020 are not correct. I figured out its because I'm using the following expression which doesn't work with leap years:

 

Calculate([some measure],DATEADD('Date'[Date],-365,DAY))

 

This works perfectly except when it's a leap year. 

 

In order to fix this I tried the following:

 

Calculate([some measure],DATEADD('Date'[Date],-1,YEAR))

 

For some reason it is includes the values for the whole month of August last year instead of only taking into account the 9 days that we have so far this month. The date table I'm using is continuous (no gaps) and includes all the dates.

 

Could someone please explain what I am doing wrong?

 

Many thanks,

 

Chris 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    stxreporting , That will happen if you use month year in visual, in that case it is getting complete month dates

     

     

    Try like

    forced=
    var _max = date(year(today())-1,month(today()),day(today()))
    return
    if(max('Date'[Date])<=_max, Calculate([some measure],DATEADD('Date'[Date],-1,YEAR)), blank())
    //or
    //calculate(Calculate([some measure],DATEADD('Date'[Date],-1,YEAR)),filter('Date','Date'[Date]<=_max))

     

    • stxreporting's avatar
      stxreporting
      Frequent Visitor
      4 years ago

      Hi,

       

      I do not have month year in my visual, just the measures for current year and for passed year (the one I sent above). I don't see how the solution you proposed will return anything other than blank(), since the max('date'[date]') will by definition always be bigger than _max right?

      Could you please elaborate?

      Thanks,

       

      Chris

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        3 years ago

        Hi, stxreporting 

        Please try formula like:

        Result=
 Calculate([some measure],DATEADD('Date'[Date],-1,YEAR),'Table'[Monthnumber]<currentYearMaxMonth)

        or apply a filter measure to table visual filter pane.

        Best Regards,
        Community Support Team _ Easonl