How to calculate Daily Total where Cummulative totals exists - using DAX

[atin]

Hi All 

Please can you tell me the DAX formulae that I can use to calculate a Daily Total.

The dataset example below contains cummulative numbers and I want to split this in order to view the Daily totals. 

See spreadsheet details below: eg April 3rd

• cummulative total column = 1,096,324.
• daily total =  82,461

How do I calculate the DAILY TOTAL  column using DAX? 

Dates & Cummulative/Daily total

Many thanks 

[v-kelly-msft · Accepted solution]

Hi @atin ，

I made a sample table  where you can refer to,pls as below:

Create measure as below:

_Daily total = 
var _previousvalue=CALCULATE(MAX('Table'[Cummulative total]),DATEADD('Table'[Date],-1,DAY))
Return
MAX('Table'[Cummulative total])-_previousvalue

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@atin , As new columns

//non continous value
last Date = maxx(filter(table,[date]<earlier([date])),[date])
daily value  = [cummaltive Total] - maxx(filter(table,[date]= earlier([last Date ])),[cummaltive Total])
// OR continous value
daily value  = [cummaltive Total] - maxx(filter(table,[date]= earlier([Date])-1),[cummaltive Total])

As measure. Take diff this date vs the last date, or when view by date , just diff of the last date

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))


This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.