Calculate daily units sold from column of cumulative units sold.

[bob57]

Here is the dataset. Description of "Cumulative Units": Hillsborough did 2 units on 2/1, 0 units on 2/2, and 2 units on 2/3 for a total of 4 units.

I need to create a measure to show a colum of "Units per Day". The table visual should look like this:

Thank you for your time an effort!

Bob

[harshnathani · Accepted solution]

Hi @bob57 ,

You can create a measure

Difference Measure =

var _pd =

CALCULATE (

MAX ( 'Table'[Date] ), FILTER(

ALLEXCEPT ( 'Table','Table'[County] ),

'Table'[Date] < MAX( ( 'Table'[Date] ))

)

)

var _pv =

CALCULATE (

MAX('Table8'[Actual]) , FILTER(

ALLEXCEPT ( 'Table', 'Table'[County] ),

'Table'[Date] = _pd

))

RETURN

MAX(Table8[Actual]) - _pv

For a Calculated Column

Difference =

var _pd =

CALCULATE (

MAX ( 'Table'[Date] ), FILTER(

ALLEXCEPT ( 'Table','Table'[County] ),

'Table'[Date] < EARLIER( ( 'Table'[Date] ))

)

)

var _pv =

CALCULATE (

MAX('Table'[Actual]) , FILTER(

ALLEXCEPT ( 'Table', 'Table'[County] ),

'Table'[Date] = EARLIER('Table8'[County])

))

RETURN

Table[Actual] - _pv

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button) 

[bob57 · Accepted solution]

I replaced 'Table8'[Actual] with 'Table'[Cumulative Units] and the measure worked perfectly. Thank you for your time, effort, and expertise.

Bob

[harshnathani · Accepted solution]

Hi @bob57 ,

Table8[Actual] refers to my dataset.

You can replace this with Cumulative Units of your data set.

Hope this help solve your problem.  Please mark as Solution if this helps.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[bob57]

Forgive me for not fully understanding your solution. To what does 'Table8'[Actual] refer? Thank you.

[harshnathani · Accepted solution]

Hi @bob57 ,

Table8[Actual] refers to my dataset.

You can replace this with Cumulative Units of your data set.

Hope this help solve your problem.  Please mark as Solution if this helps.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani · Accepted solution]

Hi @bob57 ,

You can create a measure

Difference Measure =

var _pd =

CALCULATE (

MAX ( 'Table'[Date] ), FILTER(

ALLEXCEPT ( 'Table','Table'[County] ),

'Table'[Date] < MAX( ( 'Table'[Date] ))

)

)

var _pv =

CALCULATE (

MAX('Table'[Cumulative Units]) , FILTER(

ALLEXCEPT ( 'Table', 'Table'[County] ),

'Table'[Date] = _pd

))

RETURN

MAX(Table[Cumulative Units]) - _pv

For a Calculated Column

Difference =

var _pd =

CALCULATE (

MAX ( 'Table'[Date] ), FILTER(

ALLEXCEPT ( 'Table','Table'[County] ),

'Table'[Date] < EARLIER( ( 'Table'[Date] ))

)

)

var _pv =

CALCULATE (

MAX('Table'[Cumulative Units) , FILTER(

ALLEXCEPT ( 'Table', 'Table'[County] ),

'Table'[Date] = EARLIER('Table'[County])

))

RETURN

Table[Cumulative Units]] - _pv

Regards,

Harsh Nathani

[bob57 · Accepted solution]

I replaced 'Table8'[Actual] with 'Table'[Cumulative Units] and the measure worked perfectly. Thank you for your time, effort, and expertise.

Bob

[harshnathani · Accepted solution]

Hi @bob57 ,

You can create a measure

Difference Measure =

var _pd =

CALCULATE (

MAX ( 'Table'[Date] ), FILTER(

ALLEXCEPT ( 'Table','Table'[County] ),

'Table'[Date] < MAX( ( 'Table'[Date] ))

)

)

var _pv =

CALCULATE (

MAX('Table8'[Actual]) , FILTER(

ALLEXCEPT ( 'Table', 'Table'[County] ),

'Table'[Date] = _pd

))

RETURN

MAX(Table8[Actual]) - _pv

For a Calculated Column

Difference =

var _pd =

CALCULATE (

MAX ( 'Table'[Date] ), FILTER(

ALLEXCEPT ( 'Table','Table'[County] ),

'Table'[Date] < EARLIER( ( 'Table'[Date] ))

)

)

var _pv =

CALCULATE (

MAX('Table'[Actual]) , FILTER(

ALLEXCEPT ( 'Table', 'Table'[County] ),

'Table'[Date] = EARLIER('Table8'[County])

))

RETURN

Table[Actual] - _pv

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)