Forum Discussion
DATEADD and SLICER
Hi all,
I try to make a graph for the last 7, 14, and 30...days in comparison with the same day last year. (Monday with Monday from the previous year). This is my data model:
The Last X Days table contains this data:
I'll add data to my input table. The date I'll calculate the last X days will change each day.
Everything works fine till I try to add Slicer. The first table shows correct data (there is no Filter/Slicer applied). The second table with the filter/slicer shows incorrect data.
Total kWh
Total kWh = SUM(Consumption[suma_odpoctov_kwh])
Total kWh LY
Total kWh LY = CALCULATE([Total kWh], DATEADD('Calendar'[Date], - 364, DAY))
Total kWh LY Filter
Total kWh LY Filter =
var _CurrentWeekNum =
WEEKNUM(MAX('Consumption'[den]), 1)
var _CurrentWeekDay =
WEEKDAY(MAX('Consumption'[den]), 1)
return
CALCULATE('Key Measures'[Total kWh LY],
DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH),
WEEKNUM('Calendar'[Date], 1) = _CurrentWeekNum,
WEEKDAY('Calendar'[Date], 1) = _CurrentWeekDay
)
I don't know what I'm doing wrong. How should I apply the Slicer to the first table with the correct data for the single date?
Thank you for your help.
Try using the date column from Calendar on both and change the last year measure to
Total kWh LY = CALCULATE ( [Total kWh], DATEADD ( 'Calendar'[Date], - 364, DAY ), REMOVEFILTERS ( 'Last X Days' ) )Because we changed the relationship to bidirectional the Last X Days is filtering the Calendar table. Ordinarily when you apply a new filter to the marked date table date column it will automatically remove other filters on the date table, but it does not remove cross filtering.
So when we do the DATEADD it removes any filters from Calendar and adds the dates from last year into the filter context, but this is intersected with the filter from the Last X Days table and returns an empty result set, as there are no dates both in last year and in the Last X Days table.
8 Replies
johnt75Super User
You need to change the relationship between Calendar and Last X Days to bidirectional, so that the filters can flow from the Last X Days table to Calendar.
Thank you for your reply. I changed the relationship to both. Now there are no data for the last year. I think I need to somehow change the measure of Total kWh LY.
- johnt75
A couple of questions. Is the Calendar table marked as a date table? Is the Date column in your visuals coming from the Calendar table or the Last X Days table ?
