Custom Table: Unable to refresh

[Pavlous]

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

[quentin_vigne]

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]

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]

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

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

@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 · Accepted solution]

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.