Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bob57
Helper IV
Helper IV

Calculate daily units sold from column of cumulative units sold.

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.

Table.jpg

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

Table Visual01.jpg

Thank you for your time an effort!

Bob

4 ACCEPTED SOLUTIONS
harshnathani
Community Champion
Community Champion

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) 

 
 
 

View solution in original post

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

Bob

View solution in original post

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)

 

View solution in original post

 

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

View solution in original post

5 REPLIES 5
bob57
Helper IV
Helper IV

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

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)

 

 

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

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

Bob

harshnathani
Community Champion
Community Champion

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) 

 
 
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors