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

Summing values between 2 dates returning wrong values

In my database I have various machines (see CM01, CM02 etc.) How much these machines mine is recorded by each day and is split into 2 shifts (Day or Night). This means that in the table I have there are multiple entires for the same date, but the amount mined is different for each piece of equipment (See below).

 

Shiftonnage.PNG

 

 

At the moment, I'm trying to determine how to SUM the amount mined each shift between 2 dates. I have been using this formula to do that:

 

LifeSpan = IF('FlynnsWOD'[Previous Maintanence] = BLANK(), BLANK(),
  CALCULATE('Equip_TonnesByShift'[ShiftTonnesPerEquipment running total in Date], DATESBETWEEN('Equip_TonnesByShift'[Date],FlynnsWOD[Previous Maintanence], FlynnsWOD[Date])))
 
As you can see from the table above, a piece of equipment will mine between ~1500 and at a max 2500 per shift. This would means a MAXIMUM of ~5000 per day (2 shifts). At a stretch, this means that in the table below, the CM02 entry between the 1st of April 2018 and the 5th of September 2018 (157 days) would be under 800 000 in total. For some reason, it is showing as over 7 million, and I am completely unsure why.
 

lifetonne.PNG

 

I hope this is enough information to see what I'm doing wrong, I'm assuming it is something in my formula but at this point I am completely unsure what that might be.

 

EDIT: The original formula I used returned the exact same result:

LifeTonnes = IF('FlynnsWOD'[Previous Maintanence] = BLANK(), BLANK(),
  CALCULATE(SUM('Equip_TonnesByShift'[ShiftTonnesPerEquipment]), DATESBETWEEN('Equip_TonnesByShift'[Date],FlynnsWOD[Previous Maintanence], FlynnsWOD[Date])))
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
EnsFlynn Frequent Visitor
Frequent Visitor

Re: Summing values between 2 dates returning wrong values

So it turns out that the issue was to do with the relationship between the Equipment column and the Machine column. Since it is a many to many relationship, I had to set it to a one way filter where the Machine table filtered the Equipment table. Originally, I had it in reverse to this. Hope this helps someone else.

2 REPLIES 2
EnsFlynn Frequent Visitor
Frequent Visitor

Re: Summing values between 2 dates returning wrong values

If anything is unclear please let me know, this is still plaguing me and has been for days now

Highlighted
EnsFlynn Frequent Visitor
Frequent Visitor

Re: Summing values between 2 dates returning wrong values

So it turns out that the issue was to do with the relationship between the Equipment column and the Machine column. Since it is a many to many relationship, I had to set it to a one way filter where the Machine table filtered the Equipment table. Originally, I had it in reverse to this. Hope this helps someone else.