Problems with accumulative count with a reference table

[neozhang1]

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 following as the accumulative product count curve works fine for individual table, but if I setup relationship between Calender table and other tables, and use Calender table Date as the X axis, the accume curve become the period count. 

Not sure why and want to know if there is any way to keep the same DAX for both individual table date and reference Calender table date as X axis.

Many Thanks.

TO-EWD-Acucum =

VAR varDate = SELECTEDVALUE('Turnover Schedule'[Date])

VAR varProduct = SELECTEDVALUE('Turnover Schedule'[Type Date])

RETURN

    CALCULATE(

        [TO-Count],

       'Turnover Schedule'[Date] <= varDate

            && 'Turnover Schedule'[Type Date]  = varProduct

    )

[Mrxiang]

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.

[v-yueyunzh-msft]

Hi , @neozhang1 

According to your description, when you put the 'Calendar'[Date] in the X-axis , the line will not be the rolling total.

According to your DAX, you are using SELECTEDVALUE('Turnover Schedule'[Type Date]) to get [Type Date], I have no test data and no corresponding relationship, for your [Date] and before [Type Date] It is also not very clear what the data relationship is.
First you can try to use the Matrix or Table visual object to display your value, and you can place the variable you defined separately, check whether the SELECTEDVALUE function gets the value you want, and if you need to use 'Calendar'[ The Date] field is used as the X-axis. If a relationship is created, then you can directly use the MAX(), VALUES() function to obtain the corresponding value; if no relationship is created, you need to use MAX('Calendar'[Date]) to Get the current context's date value before comparing.

Regarding your problem, without the data of the test environment, it is difficult to troubleshoot the problem for you immediately. If the above does not help you solve the problem, can you provide us with a test.pbix file without sensitive data, which can be uploaded to OneDrive Then send it to us in Link format.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly