CODEWITHSUNDEEP
powerbidax
Amie
Amie

Reputation: 103

Power BI display latest value in table

I have a dataset with Project Code, Date, Spend, and Projected Spend.

Project Code     Date       Spend      Projected Spend
 691           1/1/2020      100         1000
 691           1/2/2020      120         700 
 691           1/2/2020      80          850
 691           1/2/2020      50          850

Spend captures how much spending there was in a given month, so total spend over the timeframe will be the sum of Spend. Projected Spend is calculated as projected spend on that project at a give snapshot in time, I need to display the Latest Projected Spend value - which is not necessarily the max or the min. How do I show the Latest Projected Spend value in a table/chart?

Upvotes: 0

Views: 180

Answers (2)

Amie
Amie

Reputation: 103

I actually finally got something working that was much simpler than I expected.

Latest Projected Spend = CALCULATE(MAX(spending[Projected Project Spend]), LASTDATE(spending[Date]))

Upvotes: 1

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

You need to include a column to let Power BI know which is the latest record. Otherwise, Power BI never knows how the record have been sorted in the original data source.

One possible way is to make your Date column into a date-time column (renaming it as Timestamp here, which would sound more relevantly) so that the order of records can be uniquely identified using it.

Here is a possible definition of the Latest Projected Spend measure. Other definitions could be possible depending on your business.

Latest Projected Spend = 
SUMX (
    -- Summarizing by Project Code to iterate over each individual project,
    -- because I'm assuming that Projected Spend represents the total spending
    -- for the specific project.
    SUMMARIZE (
        Spending,
        Spending[Project Code]
    ),
    CALCULATE (
        LOOKUPVALUE (
            Spending[Projected Spend],
            Spending[Timestamp],
            MAX ( Spending[Timestamp] )
        )
    )
)

Upvotes: 1

Related Questions