Account for Weeks without Transaction Records

Goal – account for weeks without transactions.
Details –

  •      I am attempting to take Weekly average of each med over 14 weeks (example below).
  •      Sometimes items transactions do not occur in every week (10 weeks in example below).
  •      Taking average is straightforward however accounting weeks without transactions in standard deviation has proved to be challenging.



Problem – I am unable to get the correct SUM for “A-Difference” column and subsequently B-ABS+Square and C-Sum.

Question – why does it add up to 51.07 and what would be the best way to total it up to 15.7?

here are the formulas used -


CALCULATE(SUMx(‘Transactions',’Transactions'[TransactionQTY]),’Transactions'[TransactionTypes]="Return",ALLEXCEPT(' Transactions',' Transactions'[FacilityNMV2],'Transactions'[DeviceNM],'Transactions'[MedID],'Transactions'[LastTransactionLocalDTS_DateTable - Week]))


CALCULATE(SUMx(‘Transactions',’Transactions'[TransactionQTY]),’Transactions'[TransactionTypes]="Remove",ALLEXCEPT(' Transactions',' Transactions'[FacilityNMV2],'Transactions'[DeviceNM],'Transactions'[MedID],'Transactions'[LastTransactionLocalDTS_DateTable - Week]))

WeeklyRemove-Returned = WeeklyREMOVED-WeeklyRETURNED

WeeksperDataSet = used to account for all 14 weeks in the dataset.

CALCULATE(DISTINCTCOUNT('Transactions'[LastTransactionLocalDTS_DateTable - Week]),'Transactions'[TransactionTypes]="Remove",ALLEXCEPT('Transactions','Transactions'[FacilityNMV2]))

WeeklyAverage = used to account for all 14 weeks to calculate correct weekly average.


A-Difference = to calculate difference for EACH week from average.


B-ABS+Square =


C-sum =


hope you can help! Thank you in advance!

I believe that in order to get you totals correct you are going to need SUMX & SUMMARIZE . When you use these in a measure it will give you the correct overall totals. Here is one of Sam’s videos that explain this.


1 Like

this is exactly what i was looking for! Thank you!

1 Like