Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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).

 

 

 

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.
 

 

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])))
 
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.