Forum Discussion

bhelou's avatar
bhelou
Responsive Resident
4 years ago
Solved

Lastnonblank

Hello ,

i have this Data , i need to make  a measure  sum of the last ADJUSTED_COST for each ASSET_ID based on the last DEPRN_RUN_DATE.


so the table in the report  will be as follow

Asset_ID      ADJUSTED_COST

1038891       156500




 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, bhelou 

     

    Try this:

     

    lastDate = CALCULATE(MAX('Table'[DEPRN_RUN_DATE]),ALLEXCEPT('Table','Table'[ASSET_ID]))
    result = 
VAR _t =
    ADDCOLUMNS ( 'Table', "lastDate", [lastDate] )
VAR _tt =
    SUMMARIZE (
        _t,
        [ASSET_ID],
        [lastDate],
        "cost",
            CALCULATE (
                SUM ( 'Table'[ADJUSTED_COST] ),
                FILTER ( 'Table', 'Table'[DEPRN_RUN_DATE] = [lastDate] )
            )
    )
RETURN
    SUMX ( _tt, [cost] )

     

    Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

    This works well in my sample, please refer to the attachment below for details

     

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    bhelou 

    Use the following measure:

    Measure = 
var __date = MAX(Table[Dep Run Date])
var __assetid = MAX(Table[assetid])
return
CALCULATE(
    SUM(Table[adjusted cost]),
    Table[assetid] = __assetid,
    Table[Dep Run Date] = __date
)
    • bhelou's avatar
      bhelou
      Responsive Resident
      4 years ago

      Dear ,

      THe measure worked , but the sum in the table is wrong = 13 K  ( i exported to excel it gave me 25 M and is the correct figure )  it should be as the table below . 25M

      thanks ,

       

      • v-angzheng-msft's avatar
        v-angzheng-msft
        Community Support
        4 years ago

        Hi, bhelou 

         

        Try this:

         

        lastDate = CALCULATE(MAX('Table'[DEPRN_RUN_DATE]),ALLEXCEPT('Table','Table'[ASSET_ID]))
        result = 
VAR _t =
    ADDCOLUMNS ( 'Table', "lastDate", [lastDate] )
VAR _tt =
    SUMMARIZE (
        _t,
        [ASSET_ID],
        [lastDate],
        "cost",
            CALCULATE (
                SUM ( 'Table'[ADJUSTED_COST] ),
                FILTER ( 'Table', 'Table'[DEPRN_RUN_DATE] = [lastDate] )
            )
    )
RETURN
    SUMX ( _tt, [cost] )

         

        Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

        This works well in my sample, please refer to the attachment below for details

         

        Hope this helps.

         

         

        Best Regards,
        Community Support Team _ Zeon Zheng
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    bhelou  if your fact table is like this

     

    | ID | Date       | Cost |
|----|------------|------|
| 1  | 2021-01-01 | 100  |
| 1  | 2021-01-02 | 200  |
| 2  | 2021-01-05 | 500  |
| 3  | 2021-01-01 | 600  |
| 3  | 2021-01-04 | 700  |

     

    and you want to come to this

    | ID    | Date     |  Cost           |
|-------|----------|-----------------|
| 1     | 1/2/2021 |            200  |
| 2     | 1/5/2021 |            500  |
| 3     | 1/4/2021 |            700  |
| Total |          |           1400  |

     

    Please adapt the following measure to your scenario

     

    Measure2 = 
SUMX (
    'fact',
    VAR _mxDt =
        CALCULATE ( MAX ( 'fact'[Date] ), ALLEXCEPT ( 'fact', 'fact'[ID] ) )
    VAR _mxID =
        CALCULATE ( MAX ( 'fact'[ID] ) )
    VAR _mxCost =
        CALCULATE (
            SUM ( 'fact'[Cost] ),
            TREATAS ( { ( { _mxDt }, { _mxID } ) }, 'fact'[Date], 'fact'[ID] )
        )
    RETURN
        _mxCost
)