Account for Weeks without Transaction Records

DAX DAX Calculations
1

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.

image003

image002

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 -

WeeklyRETURNED =

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

WeeklyREMOVED =

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.

CALCULATE([WeeklyRemoved-Returned]/[WeeksperDataSet],or('Transactions'[TransactionTypes]="Remove",'Transactions'[TransactionTypes]="Return"),ALLEXCEPT('Transactions','Transactions'[FacilityNMV2],'Transactions'[DeviceNM],'Transactions'[MedID]))

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

[WeeklyRemoved-Returned]-[WeeklyAverage]

B-ABS+Square =

ABS([A-Difference])^2

C-sum =

CALCULATE(SUMX('Transactions',[B-ABS+Square]),ALLEXCEPT('Transactions','Transactions'[FacilityNMV2],'Transactions'[DeviceNM],'Transactions'[MedID]))

hope you can help! Thank you in advance!

2

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.

Enterprise%20DNA%20Expert%20-%20Small

1 Like
3

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

1 Like