Basic MTD Sales Calculation Not Summing Individual Days

[jamesgbrooks]

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?

[Thejeswar]

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

[jamesgbrooks]

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

[Thejeswar · Accepted solution]

@jamesgbrooks,

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

[jamesgbrooks]

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!