Forum Discussion

Pavlous's avatar
Pavlous
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Custom Table: Unable to refresh

Hi I have this function for making summary table from 2 other tables which are data sources (Archive is excel export from SharePoint) and CAS TASKS are sharepoint items from list Result = VAR cale...
Need Help
  • Pavlous's avatar
    Pavlous
    8 years ago

    Hello,

    formula was and is absolutely OK. It seeems like the PowerBI is unable to find correct first / last date. Whatever, its working once again.

quentin_vigne's avatar
quentin_vigne
Icon for Solution Sage rankSolution Sage
8 years ago

Hi Pavlous

 

I also had this kind of error with a formula close to yours, I was using a database where one of the operator filled our file with the 'start date' = 18/12/2018 instead of 18/12/2017.

The end date was early 2018 so when making the calculated column I was having an error because of this record.

 

Try to check your files if there is a mistake ? 

 

- Quentin

 

(edited the post)

  • Pavlous's avatar
    Pavlous
    Icon for Advocate II rankAdvocate II
    8 years ago

    Hi quentin_vigne,

    So I have checked the files in PowerBI and it seems that our customer archived some record with nonsense 'Received Date'. So the Start date was greater than end date. However, when i filtered it out and double-checked that this condition is fullfilled, it still shows the same error message.

     

    However thank you for your tip :)

    • quentin_vigne's avatar
      quentin_vigne
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Pavlous (i've edited the above message because I've swapped end date and start date, it makes more sense now)

       

      Can you check what's the result of : 

       

      CALCULATE ( FIRSTDATE (Archive[Received Date]), Archive[Categories] = "Agreements" )

       and the result of : 

       

      ((FIRSTDATE (Archive[Received Date]))-LASTDATE('CAS Tasks'[EndTime])) 
      • Pavlous's avatar
        Pavlous
        Icon for Advocate II rankAdvocate II
        8 years ago

        quentin_vigne oh, sorry that was attempt of debugging original formula is without the -

        -((FIRSTDATE (Archive[Received Date]))-LASTDATE('CAS Tasks'[EndTime]))  

        part :)

         

        Its a bit weird. When I filtered out all results from Archive that are after 31/12/2017 it started working. But its nonsense, even though the results before filtering were before the EndTime :(