Forum Discussion
Filter context weirdness
paulpassot
Aparently this is Auto-Exist problem.First step try to manually calculate the last day sales. If it does not match the result of my first solution then you need to have a date table that filters your data set. This will totally eleiminate the problem.
Hi, thanks for the quick reply!
Interestingly, it gives the same result across dates (which is good), but it doesn't seem to give the right number:
I would expect 4,744$, not 5,358$.
I understand the logic of your code though, and I can't figure out how it comes up to 5,358$
paulpassot
I don't know how your data looks like but seems to me there are other filters. You may try **bleep** this way
Max Day Sales =
VAR MaxDate =
CALCULATE (
MAX ( Invoices_LQ[PostDate_DateFormat] ),
ALL ( Invoices_LQ[PostDate_DateFormat] )
)
RETURN
CALCULATE (
SUM ( Invoices_LQ[InvoiceLine.Amount] ),
Invoices_LQ[PostDate_DateFormat] = MaxDate,
ALL ( Invoices_LQ[PostDate_DateFormat] )
)So strange... it gives me the same result as my initial formula. Randomly giving a different number depending on what I select in another visual:
Here it is ok, filtered on 21st of june
Here it gives me a different value (wrong) when filtered on the 20th of june:
My datamodel is incredibly simple: single table with sales transactions in rows, a column for price and a column for date. I made a calculated column that "rounds" the post date (which is datetime) to a regular date :
PostDate_DateFormat = DATE(YEAR(Invoices_LQ[InvoiceHeader.Column1.invoicePostDate]),MONTH(Invoices_LQ[InvoiceHeader.Column1.invoicePostDate]),DAY(Invoices_LQ[InvoiceHeader.Column1.invoicePostDate]))That's the column I leverage in my Max Day Sales formula. Do you think it could come from there?paulpassot
Aparently this is Auto-Exist problem.First step try to manually calculate the last day sales. If it does not match the result of my first solution then you need to have a date table that filters your data set. This will totally eleiminate the problem.