Date Table Problem - Calculate Sum Missing Data Outside Filter Window

[cdfizz17]

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?

[FBergamaschi]

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

[cdfizz17]

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.

[amitchandak · Accepted solution]

@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.

[cdfizz17]

Thank you, this fixed my problem.

Period filter needed to be removed 🙂