Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
This worked for me: CALCULATE(SUM('DataTable'[Production]), 'DataTable'[Attribute1] = "specific account",DATEADD('DateTable'[Date],-1,DAY))
Thanks all!
This worked for me: CALCULATE(SUM('DataTable'[Production]), 'DataTable'[Attribute1] = "specific account",DATEADD('DateTable'[Date],-1,DAY))
Thanks all!
@EMP 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
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
hi @EMP
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]?
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |