Slicer sometimes does not work

[kalo1314]

Hi all,

I have encountered a technical problem and need for a kind help. 

I am trying to make a formula for a 12-month cumulative calculation.  I have made a date table to connect two fact tables. I also create a 12-month slicer table to connect to the date table in a bidirectional way to make the slicer effective. However, the similar calculation is good on one table, but the other one have some duplicated values, showing that the slicer does not work well.  Please see below:

The good result of the calculation can be seen below:

ComFATLTIroll = CALCULATE(SUM('Cority Data'[FATLTI Count]), REMOVEFILTERS('Date Period'[Period]),DATESINPERIOD('Calendar'[CalDate], LASTDATE('Calendar'[CalDate]), -12,MONTH))

The bad result of the calculation is:

ComManhoursRoll = CALCULATE(SUM('CorityMHL'[Manhours]), REMOVEFILTERS('Date Period'[Period]),DATESINPERIOD('Calendar'[CalDate], LASTDATE('Calendar'[CalDate]),-12,MONTH))

The reason I use REMOVEFILTERS in the calculation is that I need the result unchanged when the month is rolling.  The period slicer is just like below:

I have checked the data types are all correct.  Can anyone give a hand to solve my problem?

[Wilson_ · Accepted solution]

Hi kalo,

This looks like a modeling problem. You should not need a Date Period table; any additional dimensional data should be in your Calendar table. Also, it looks like you have a table that filters your Date Period table, which is almost certainly incorrect modeling.

Hard to give much more feedback than that given the limited view of the model itself. I just don't think this is primarily a DAX problem.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.