Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
How do I calculate the DAILY TOTAL column using DAX?
Many thanks
Solved! Go to 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.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |