Forum Discussion
Basic MTD Sales Calculation Not Summing Individual Days
Hello,
I am having issues with my MTD Sales calculation. It should be summing up cumulative sales through the month, but instead, it is outputting the exact same value as the total cost forumla shown below.
For example, on June 2nd, the MTD cost should be (sum of cost from June 1 + sum of total cost from June 2). Instead, it is merely showing the total cost for June 2 at the MTD Sales value.
Can you someone please assist and let me know what I am doing wrong?
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
4 Replies
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 tableYou 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
