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 paulpassot
please try
Max Day Sales =
VAR MaxDate =
CALCULATE ( MAX ( Invoices_LQ[PostDate_DateFormat] ), REMOVEFILTERS () )
RETURN
CALCULATE (
SUM ( Invoices_LQ[InvoiceLine.Amount] ),
Invoices_LQ[PostDate_DateFormat] = MaxDate,
REMOVEFILTERS ()
)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 wayMax 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?
