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.

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.