Forum Discussion
Basic MTD Sales Calculation Not Summing Individual Days
You are getting this error because your fact table and Calendar table are related using an indirect relationship i.e. using some other table these two tables are already joined
From your screenshot, it can be seen that you fact and calendar tables are joined using uvwDimDeliveryDate table.
As it says, disable indirect relationship or change the cross filtering direction to single and then create a direct relationship between the fact and calendar tables. If done so, the already given measures will work.
If that is not possible, In such case, what you can do is, create an inactive relationship between these tables
1. Create a measure that will have the mny cost based on the inactive relationship as below
mnycostmeasure = CALCULATE(sum(Fact[mnycost]), USERELATIONSHIP(Fact[Date], Calendar[Date]))
2. Then create a TOTALMTD() based on the newly created measure
Measure 4 = TOTALMTD(mnycostmeasure,DateKey[Date])
Hope this solves your issue
You are getting this error because your fact table and Calendar table are related using an indirect relationship i.e. using some other table these two tables are already joined
From your screenshot, it can be seen that you fact and calendar tables are joined using uvwDimDeliveryDate table.
As it says, disable indirect relationship or change the cross filtering direction to single and then create a direct relationship between the fact and calendar tables. If done so, the already given measures will work.
If that is not possible, In such case, what you can do is, create an inactive relationship between these tables
1. Create a measure that will have the mny cost based on the inactive relationship as below
mnycostmeasure = CALCULATE(sum(Fact[mnycost]), USERELATIONSHIP(Fact[Date], Calendar[Date]))
2. Then create a TOTALMTD() based on the newly created measure
Measure 4 = TOTALMTD(mnycostmeasure,DateKey[Date])
Hope this solves your issue
Thank you! I disabled the relationship between the fact table and uvwdimdate and then created a relationship between the fact and calendar table and now the MTD sales column is summing properly!