Forum Discussion

neozhang1's avatar
neozhang1
Regular Visitor
3 years ago

Problems with accumulative count with a reference table

Hi all,   I have multiple tables, one table as reference Calender table for date fitler, such year, quarter, month etc., and the rest for different types of product and their selling date.  I used...
Mrxiang's avatar
Mrxiang
Helper II
3 years ago
It sounds like you are experiencing a problem with your DAX formula when using a reference Calendar table for date filtering. The issue may be related to the relationship between the Calendar table and the other tables.

One possible solution is to modify your DAX formula to include the Calendar table in the calculation. You can try using the RELATED function to access the Calendar table and retrieve the date values for filtering.

Here is an example of how you can modify your DAX formula:

TO-EWD-Acucum =
VAR varDate = SELECTEDVALUE('Calendar'[Date])
VAR varProduct = SELECTEDVALUE('Turnover Schedule'[Type Date])
RETURN
CALCULATE(
[TO-Count],
FILTER(
'Turnover Schedule',
'Turnover Schedule'[Date] <= varDate
&& 'Turnover Schedule'[Type Date]  = varProduct
&& 'Turnover Schedule'[Date] = RELATED('Calendar'[Date])
)
)

This modified formula should allow you to use the same DAX for both individual table date and reference Calendar table date as X axis.