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
Hi jamesgbrooks,
Your MTD DAX is correct.
I think there should be some problem like absense of join between your FACT Table and Calenda Table, which is causing the issue
Note: I was able to re-create the error when I removed the relationship between my Fact table and Calendar table.
For DATESMTD, alternatively you can use TOTALMTD() Function as well
I tried the same and below is my output screenshot
Measure 3 = CALCULATE(SUM(Table1[No.of bookings_2]), DATESMTD('DateKey'[Date]))
Measure 4 = TOTALMTD(SUM(Table1[No.of bookings_2]),DateKey[Date])
Here Datekey is my Calendar table
You are correct. I just took a look at my relationships and there is no link between Calendar and Fact table... But this brings up another issue.
In the fact table, there are three different dates:
1)the date and time of when the order was placed
2)the date and time of the status of the order (if the status changes from order placed to order completed this accounts for this)
3) the date and time of the first time an order was delivered (this is different that status = complete and is necessary)
If I attempt to link any of the three back to the fact table in "manage relationships", I get the following error:
Here are current relationships, one is between delivery date and calendar
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!