Forum Discussion

Juli2102's avatar
Juli2102
Frequent Visitor
3 years ago
Solved

Calculating difference in historical data for multiple items

Hello,    I am struggling with some calculations in PowerBI: I have a data table that contains about 100 vehicles with multipe columns and appropriate information. This data table is a SharePoint...
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Juli2102 ,

     

    Here are the steps you can follow：

    1. Create measure.

     

    Measure =
var _current=
SUMX(
FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]=MAX('Table'[Timestamp])),[Price])
var _next=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Vehicle]=MAX('Table'[Vehicle])&&
    'Table'[Timestamp]=
    MAXX(FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]<MAX('Table'[Timestamp])),[Timestamp])),
     [Price])
return
IF(
    _next= BLANK(),
    0,
    _current - _next)

    OR:

    Measure 2 =
var _current=
SUMX(
    FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Number of Row]=MAX('Table'[Number of Row])),[Price])
var _next=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Vehicle]=MAX('Table'[Vehicle])&&
    'Table'[Number of Row]=MAX('Table'[Number of Row])+1),
     [Price])
return
IF(
    _next= BLANK(),
    0,
    _current - _next)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  Juli2102 ,

 

Here are the steps you can follow：

1. Create measure.

 

Measure =
var _current=
SUMX(
FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]=MAX('Table'[Timestamp])),[Price])
var _next=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Vehicle]=MAX('Table'[Vehicle])&&
    'Table'[Timestamp]=
    MAXX(FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Timestamp]<MAX('Table'[Timestamp])),[Timestamp])),
     [Price])
return
IF(
    _next= BLANK(),
    0,
    _current - _next)

OR:

Measure 2 =
var _current=
SUMX(
    FILTER(ALL('Table'),'Table'[Vehicle]=MAX('Table'[Vehicle])&&'Table'[Number of Row]=MAX('Table'[Number of Row])),[Price])
var _next=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Vehicle]=MAX('Table'[Vehicle])&&
    'Table'[Number of Row]=MAX('Table'[Number of Row])+1),
     [Price])
return
IF(
    _next= BLANK(),
    0,
    _current - _next)

2. Result:

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly