Calculate Previous Days Production, Ignore Slicer Date Range Limitation

[Anonymous]

Geetings. In need of help please.

I have 2 tables, a date table and a data table, relationship = 'DateTable'[Date] to 'DataTable[GLDate]. 

I have a page in BI Desktop with a date slicer ('DateTable'[Date]). For this example it's set to 9/1/22 - 9/30/22.

I want to bring in the previous days production values. The result I am able to achieve (re-created in Excel for simplicity/sensitive data) is this:

Where 9/1 is blank because the previous day (8/31) is outside the range of the date slicer. I've somehow managed to produce this result like 8 different ways. 

What I want is this:

Where the value displays in 9/1 for the prior day, despite said prior day being outside the range of the date slicer. 

I have tried creating various calculated columns and measures, no luck. Been at this for hours. 

Any help is much appreciated.

[Anonymous · Accepted solution]

This worked for me: CALCULATE(SUM('DataTable'[Production]), 'DataTable'[Attribute1] = "specific account",DATEADD('DateTable'[Date],-1,DAY))

Thanks all!

[Greg_Deckler]

@Anonymous You need to use ALL to break out of the slicer context (or ALLEXCEPT) and then filter back to the previous day. Something like:

Prev Day Measure =
  VAR __Date = MAX('DateTable'[Date])
  VAR __Table = ALL('Table')
  VAR __Result = SUMX(FILTER(__Table, [Date] = __Date - 1),[Value Booked])
RETURN
  __Result

[Anonymous]

Appreciate the input, unfortunately this did not work for me. Not sure what it brought back but it wasn't the correct values.

Here's what I entered for the measure:

Prev Day Measure = 

VAR _Date = MAX('DateTable'[Date])

VAR _Table = ALL('DataTable')

VAR _Result = SUMX(FILTER(_Table, 'DataTable'[GLDate] = _DATE -1),'DataTable'[Production])

RETURN

_Result

[FreemanZ]

hi @Anonymous 

there are multiple ways to ignore a filter context (including slicer) to a column, like adding:

ALL(DateTable'[Date])

into your code. 

What code do you have for [Pre Day]?

[Anonymous]

I tried nesting in ALL, but I must have done it wrong because the result was literally ALL (the grand total repeated on each row) - it ignored the dates completly. 

I seriously have achieved this same result like 8 different ways, but the measure I currently have is:

CALCULATE(SUM('DataTable'[Production]), 'DataTable'[Attribute1] = "specific account",DATEADD('DateTable'[Date],-1,DAY))

I tried this version with ALL, but it brings back the exact same results (9/1 = blank) as the previously mentioned measure:

CALCULATE(SUM('DataTable'[Production]), 'DataTable'[Attribute1] = "specific account",ALL('DateTable'[Date],DATEADD('DateTable'[Date],-1,DAY))