getting daily actuals from cumulative figures

[lewil]

HI all,

if i have a table which comprises of a date field and a cumulative column field, is there a measure i can use to calculate the actual daily figures on a day by day basis.

need this to work on both a table and a graph.

Got this working for a table but line graph just showed the cumulative figure when i used the daily actual confirm measure. so the graph showed values in the 20000 range instead of the 0-100 range

Country/Region	Date	Yesterdays Confirmed	Confirmed	Daily actuals confirm
Angola	28/01/2021 00:00	19580	19672	92
Angola	27/01/2021 00:00	19553	19580	27
Angola	26/01/2021 00:00	19476	19553	77

Confirmed =

var LatestDate = MAX('tValues'[Date])

return

CALCULATE(SUM(tValues[Confirmed Cases]), 'tValues'[Date] = LatestDate)

Yesterdays Confirmed =

CALCULATE(SUM(tValues[Confirmed Cases]),

PREVIOUSDAY('Calendar'[Date]))

daily actuals confirm =

var LatestDate = MAX('tValues'[Date])

var todayconf = CALCULATE(SUM(tValues[Confirmed Cases]), 'tValues'[Date] = LatestDate)

Var yesterdayconf = CALCULATE(SUM(tValues[Confirmed Cases]), PREVIOUSDAY('Calendar'[Date]))

return

todayconf - yesterdayconf

Thanks

[v-robertq-msft · Accepted solution]

Hi, @lewil 

According to your description, you said that the measure [daily actuals confirm] can display the correct value when it’s placed in a table chart, but incorrect in the line chart. Right? In my opinion, if you have created the measure [Confirmed] and [Yesterdays Confirmed], you can just calculate the [daily actuals confirm] like this:

Daily actuals confirm =

[Confirmed1]-[Yesterdays Confirmed1]

Then I created a column chart and place like this, the measure can display the correct value, like this:

And you can get what you want.

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[parry2k]

@lewil add a new column using the following DAX expression and it will get your daily value from the cumulative total.

Sales = 
VAR __currentDate = Sales[Date]
VAR __previousDate = 
CALCULATE ( 
    LASTDATE ( Sales[Date] ), 
    Sales[Date] < __currentDate 
)
VAR __previousSales = 
CALCULATE ( 
    MAX ( Sales[Cumulative Sales] ), 
    Sales[Date] = __previousDate 
)
RETURN
Sales[Cumulative Sales] - __previousSales

Change table and column name as per your model.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak]

@lewil , You can try a measure like

sum(Table[Confirmed]) - sumx(filter(Table, [Country/Region] = max([Country/Region]) && [Date] =max([Date]) -1),[Confirmed])

or try time intelligence

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...