Summarizing data from previous date

[cheid_4838]

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

[bhanu_gautam · Accepted solution]

@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]
)

[cheid_4838]

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.

[Anonymous]

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

[cheid_4838]

Thanks for the help. Do you know why I am getting this message? I have never seen this before.