Forum Discussion
Date Table Problem - Calculate Sum Missing Data Outside Filter Window
I have a Date table and a Sales table, with measures for Total Sales and 'Total Sales Offset' (Total Sales shifted by 1 month):
TotalSales = CALCULATE(
SUM('Sales'[Total])
)
TotalSalesOffset = CALCULATE(
[TotalSales],
DATEADD('Date'[Date],-1,MONTH)
)
Using a date slicer to set the time window, these measures work as expected. However, the client has asked for a configurable tile slicer to replace the date filter.
To accomplish this, I created a third table 'Periods' which cross-filters the Date table:
This has broken the 'Total Sales Offset' measure, see below:
Working as expectedMissing data
How can I fix this measure so that it correctly displays the data from outside the date window?
cdfizz17 , In this case date table filter is getting removed to calculate the prior period, but of period filter is not getting removed.
try if all period can help
TotalSalesOffset = CALCULATE(
[TotalSales],
DATEADD('Date'[Date],-1,MONTH), all(period)
)If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
4 Replies
In this measure
TotalSales = CALCULATE( SUM('Sales'[Total]) )You can remove the CALCULATE part
To answer your question I need to see the content of the table Periods and understand what you filter (full period or focus how are they determined)
Anyway you have setup a many to 1 connection bidirezionale that I assume is the cause of the issue but I will confirm this
Thank you for your response. The table 'Periods' is structured as below:
Date Filter 2024/06/01 Full Period 2024/06/02 Full Period 2024/06/03 Full Period 2024/06/04 Full Period ... ... 2024/12/29 Full Period 2024/12/29 Focus Period 2024/12/30 Full Period 2024/12/30 Focus Period 2024/12/31 Full Period 2024/12/31 Focus Period 'Full Period' is intended to cross-filter every date.
'Focus Period' is intended to filter a sub-set of dates, e.g. Dec-24.
cdfizz17 , In this case date table filter is getting removed to calculate the prior period, but of period filter is not getting removed.
try if all period can help
TotalSalesOffset = CALCULATE(
[TotalSales],
DATEADD('Date'[Date],-1,MONTH), all(period)
)If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
