Forum Discussion

davidell's avatar
davidell
Frequent Visitor
9 years ago

YTD time intelligence wrong numbers-Odd behavior?

Hi PBI community, I have encounted a problem with performing simple time intelligence functions on dataset. I’ve crawled the internet for a solution sadly without any luck, where as I hope some of y...
Need Help
bsas's avatar
bsas
Icon for Post Patron rankPost Patron
9 years ago

Hi davidell,

 

You need to use MIN and MAX in calculate. E.G Calculate(SUM([sales]), Filter(table, and( your end date <= Max(alldates.[date]), your end date >= Min(alldates.[date]))))

 

or 

 

total sale during 2015/1/1 to 2015/7/20 = CALCULATE(SUM(Sales[SALE]),AND(Sales[DATE]<=DATE(2015,7,20),Sales[DATE]>=DATE(2015,1,1)))

 

Also you can searc in YTD solved problems here. There are a lot of similar topics.

  • davidell's avatar
    davidell
    Frequent Visitor
    9 years ago

    Hi bsas,

    Thank you very much for your reply.

    I've tried what you said and it works out. However I want the YTD to automatically detect the current year and compare with other years.

     

    Basically I want to create a matrix in which I can compare YTD, LY and change between YTD and LY across different dates such as 2010,2011,2012,...2018. 

    Since I'm not a DAX jedi yet, I kind of lack the insight in DAX.

    I've tried searching the forum and found many similar problems, however none of them which I could get to work:


    /David


    • bsas's avatar
      bsas
      Icon for Post Patron rankPost Patron
      9 years ago

      davidell

       

      If you wanna detect current year then use Year(Today()) in your calculations. I'll look for a solution in the evening.

      • davidell's avatar
        davidell
        Frequent Visitor
        9 years ago

        Thanks alot bsas I really appreciate your help.

        Just a side note. It's pretty important for me to be able to specify a start date, since it's fiscal years I'm going to calculate, and it's not starting 1/1.

        /David