Forum Discussion

Pavlous's avatar
Pavlous
Advocate 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.

Pavlous's avatar
Pavlous
Advocate 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
Solution 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
    Advocate 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 :( 

    • quentin_vigne's avatar
      quentin_vigne
      Solution Sage
      8 years ago

      Pavlous

       

      There is maybe a record that's still wrong ...

       

      CALCULATE ( FIRSTDATE (Archive[Received Date]), Archive[Categories] = "Agreements" ),
        CALCULATE ( LASTDATE('CAS Tasks'[EndTime]), 'CAS Tasks'[Category] = "Agreements" ))

      This formula looks correct, but is the result correct ? (First date before Lastdate ?) 

      • Pavlous's avatar
        Pavlous
        Advocate II
        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.