Forum Discussion

cheid_4838's avatar
cheid_4838
Helper IV
1 year ago
Solved

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 a...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

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

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
    cheid_4838
    Helper IV
    1 year ago

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

     

  • cheid_4838's avatar
    cheid_4838
    Helper IV
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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