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 calendar_date =
    CALENDAR (
        CALCULATE ( FIRSTDATE (Archive[Received Date]), Archive[Categories] = "Agreements" ),
        CALCULATE ( LASTDATE('CAS Tasks'[EndTime]), 'CAS Tasks'[Category] = "Agreements" ))
		//^^ this part takes first and last relevant date 
    
VAR filted =
    FILTER ( ALL ( 'CAS Tasks' ), [Category] = "Agreements" )  //First variable to establish data from CAS Table
VAR filted2=
	FILTER(ALL('Archive'),[Categories] = "Agreements")  //Second variable to establish data from Archive
RETURN                   //Returning data into new table
    ADDCOLUMNS (
        calendar_date,
        "Total", (SUMX (
            FILTER (
                ADDCOLUMNS (
                    filted,
                    "Exist", CONTAINS (
                        SELECTCOLUMNS ( CALENDAR ( [Received Date], 'CAS Tasks'[EndTime] ), "CheckDate", [Date] ),
                        [CheckDate], [Date]
                    )
                ),
                [Exist] = TRUE ()
            ),
            'CAS Tasks'[To process corr.]  // Hopefully addition to "TOTAL" from archive and SP data
    ))+
 (
    SUMX (
        FILTER (
            ADDCOLUMNS (
                filted2,
                "Exist2", CONTAINS (
                    SELECTCOLUMNS ( CALENDAR ( [Received Date], [End Time] ), "CheckDate2", [Date] ),
                    [CheckDate2], [Date]
                )
            ),
            [Exist2] = TRUE ()
        ),
        Archive[To process corr.]
    )
)
        )

And I gets this error message when I try to refresh. I am sure that the calendar starting date is no lower than the end date. Anyone has an idea where should I look?

 

Error message

  • 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.

6 Replies

  • 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
      Advocate II

      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

        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]))