Forum Discussion
Summarizing data from previous date
I have a database that lists all pick-ups for my business. I am wanting to create a summarized view of potential pickups within the same state and/or customer that we delivered to the previous day and/or within the same week. For example we made deliveries to SC on 1/6 and had another pick-up in SC on 1/7 or another day within the week. I would like to summarize the data as shown below. I also would like to see those states that did not have another pickup the following day. For example we delivered to NC on 1/7, but no pickups the following day or within the same week. Any suggestions for how this would work in DAX? Thanks for your help.
| OriginState | ShipperName | DestState | ConsigneeName | Ship Date | Delivery Date | LDMiles | MTMiles | Vol |
| TN | DOMTAR | SC | SOUTHEASTERN PAPERBOARD | 1/6/25 | 1/6/25 | 163 | 163 | 1 |
| TN | DOMTAR PAPER COMPANY | SC | SOUTHEASTERN PAPERBOARD | 1/6/25 | 1/6/25 | 163 | 163 | 1 |
| GA | DALTON RECYCLING PLANT | TN | DOMTAR PAPER COMPANY | 1/6/25 | 1/6/25 | 247 | 247 | 1 |
| NC | ALDI,INC | TN | DOMTAR PAPER COMPANY | 1/6/25 | 1/6/25 | 976 | 392 | 3 |
| NC | JC PENNEY | TN | DOMTAR PAPER COMPANY | 1/6/25 | 1/6/25 | 191 | 191 | 1 |
| TN | DOMTAR PAPER COMPANY | NC | PINNACLE CORRUGATED | 1/7/25 | 1/7/25 | 1090 | 0 | 5 |
| TN | DOMTAR | SC | SOUTHEASTERN PAPERBOARD | 1/7/25 | 1/7/25 | 326 | 0 | 2 |
| GA | DALTON RECYCLING PLANT | TN | DOMTAR PAPER COMPANY | 1/7/25 | 1/7/25 | 247 | 247 | 1 |
| SC | CANUSA HERSHMAN RECYCLING | TN | DOMTAR PAPER COMPANY | 1/7/25 | 1/7/25 | 314 | 68 | 2 |
| SC | PRATT RECYCLING | TN | DOMTAR PAPER COMPANY | 1/7/25 | 1/7/25 | 215 | 215 | 1 |
| TN | WALMART | TN | DOMTAR PAPER COMPANY | 1/7/25 | 1/7/25 | 54 | 54 | 1 |
| Result | LDMiles | MTMiles | Vol | |||||
| SC | 529 | 283 | 3 | |||||
| TN | 1470 | 54 | 8 |
cheid_4838 Create a Calculated Column for Week Number
WeekNumber = WEEKNUM('Table'[Ship Date], 2)
Create a Calculated Column for Previous Day
PreviousDay = 'Table'[Ship Date] - 1
Measure for LDMiles
dax
TotalLDMiles =
CALCULATE(
SUM('Table'[LDMiles]),
FILTER(
'Table',
'Table'[DestState] = EARLIER('Table'[DestState]) &&
(
'Table'[Ship Date] = EARLIER('Table'[PreviousDay]) ||
'Table'[WeekNumber] = EARLIER('Table'[WeekNumber])
)
)
)Measure for MTMiles
dax
TotalMTMiles =
CALCULATE(
SUM('Table'[MTMiles]),
FILTER(
'Table',
'Table'[DestState] = EARLIER('Table'[DestState]) &&
(
'Table'[Ship Date] = EARLIER('Table'[PreviousDay]) ||
'Table'[WeekNumber] = EARLIER('Table'[WeekNumber])
)
)
)Measure for Vol
dax
TotalVol =
CALCULATE(
SUM('Table'[Vol]),
FILTER(
'Table',
'Table'[DestState] = EARLIER('Table'[DestState]) &&
(
'Table'[Ship Date] = EARLIER('Table'[PreviousDay]) ||
'Table'[WeekNumber] = EARLIER('Table'[WeekNumber])
)
)
)You can create a summarized table to display the results:
dax
SummaryTable =
SUMMARIZE(
'Table',
'Table'[DestState],
"Total LDMiles", [TotalLDMiles],
"Total MTMiles", [TotalMTMiles],
"Total Vol", [TotalVol]
)
4 Replies
cheid_4838 Create a Calculated Column for Week Number
WeekNumber = WEEKNUM('Table'[Ship Date], 2)
Create a Calculated Column for Previous Day
PreviousDay = 'Table'[Ship Date] - 1
Measure for LDMiles
dax
TotalLDMiles =
CALCULATE(
SUM('Table'[LDMiles]),
FILTER(
'Table',
'Table'[DestState] = EARLIER('Table'[DestState]) &&
(
'Table'[Ship Date] = EARLIER('Table'[PreviousDay]) ||
'Table'[WeekNumber] = EARLIER('Table'[WeekNumber])
)
)
)Measure for MTMiles
dax
TotalMTMiles =
CALCULATE(
SUM('Table'[MTMiles]),
FILTER(
'Table',
'Table'[DestState] = EARLIER('Table'[DestState]) &&
(
'Table'[Ship Date] = EARLIER('Table'[PreviousDay]) ||
'Table'[WeekNumber] = EARLIER('Table'[WeekNumber])
)
)
)Measure for Vol
dax
TotalVol =
CALCULATE(
SUM('Table'[Vol]),
FILTER(
'Table',
'Table'[DestState] = EARLIER('Table'[DestState]) &&
(
'Table'[Ship Date] = EARLIER('Table'[PreviousDay]) ||
'Table'[WeekNumber] = EARLIER('Table'[WeekNumber])
)
)
)You can create a summarized table to display the results:
dax
SummaryTable =
SUMMARIZE(
'Table',
'Table'[DestState],
"Total LDMiles", [TotalLDMiles],
"Total MTMiles", [TotalMTMiles],
"Total Vol", [TotalVol]
)Thanks for the help. Do you know why I am getting this message? I have never seen this before.
I think I figure out the measures noted above. I was able to get the measures to work when I converted them to a column. Now the totals I am getting our much higher than they should be. The table on the left is what I get with the logic noted in the above solution for a one week period. The table on the right is the result of using the raw data. I would expect to see that the deststate TN would have a LDMiles total of 4,102 not 283,049. Is this a result of using a column instead of measures? Thanks.
Hi cheid_4838,
In fact, measure and calculated column are work on different data levels and you can't use the child level to affect its parent level.
When you use calculated column to replace measure formula, their results will be fixed and not able to respond with filter interaction from slicer/filter.Notice: the data level of power bi(from parent to child level)
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)
Regards,
Xiaoxn Sheng
