Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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)
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 🙂
@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]))
@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 😞
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 ?)
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |