cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesgbrooks Frequent Visitor
Frequent Visitor

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?

 

 

Capture.JPG

 

 

 

Capture1.JPG

 

Capture2.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Thejeswar Senior Member
Senior Member

Re: Basic MTD Sales Calculation Not Summing Individual Days

@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

4 REPLIES 4
Thejeswar Senior Member
Senior Member

Re: Basic MTD Sales Calculation Not Summing Individual Days

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

TOTALMTD_DATESMTD.PNG

 

 

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 Frequent Visitor
Frequent Visitor

Re: Basic MTD Sales Calculation Not Summing Individual Days

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:

 

Capture2.JPG

Here are current relationships, one is between delivery date and calendar

Capture.JPG

Thejeswar Senior Member
Senior Member

Re: Basic MTD Sales Calculation Not Summing Individual Days

@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

Highlighted
jamesgbrooks Frequent Visitor
Frequent Visitor

Re: Basic MTD Sales Calculation Not Summing Individual Days

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!