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_...
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.

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.

  • bhelou's avatar
    bhelou
    Responsive Resident
    4 years ago

    Many Thanks , it worked