Forum Discussion

paulpassot's avatar
paulpassot
Frequent Visitor
4 years ago
Solved

Filter context weirdness

Hi there!   Perhaps a newbie question, but I can't wrap my head around how a measure reacts to filters...  I have a measure that calculates sum of sales for the most recent day in the data :   ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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] )
    )
paulpassot's avatar
paulpassot
Frequent Visitor
4 years ago

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?

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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.