How to have new column/measure showing the difference of the data from previous dates

[jovendeluna21]

Hello,

Any idea on how to have new column showing the difference of the data from previous date. I just want to have daily distributed column since the total_distributed is cumulative.

For example, January 13, 2021 total_distributed will subtract to January 12 total_distributed (1,984,225 - 1,949,125 = 35,100), January 14, 2021 to January 13, 2021 (2,105,600 - 1,984,225 = 121,375) and so on. The challenge is location since it must harmonized to specific a location.

Attached herewith the pbi file.

https://drive.google.com/file/d/1jeGo2XIWc3oBwAkfWf4ScZISuXL4FKqo/view?usp=sharing

Thanks!

[jovendeluna21]

Is it possible to be a column instead of a measure?

[mahoneypat · Accepted solution]

Here is a measure expression that shows one way to do it.

Daily Distribution =
VAR vThisDate =
    MAX ( us_state_vaccinations[date] )
VAR vThisValue =
    SUM ( us_state_vaccinations[total_distributed] )
VAR vPrevValue =
    CALCULATE (
        LASTNONBLANKVALUE (
            us_state_vaccinations[date],
            CALCULATE (
                SUM ( us_state_vaccinations[total_distributed] )
            )
        ),
        ALL ( us_state_vaccinations[date] ),
        us_state_vaccinations[date] < vThisDate
    )
VAR vResult = vThisValue - vPrevValue
RETURN
    vResult

Pat