Forum Discussion
getting total or average for a specific year
Hi,
As I have to get total and average amount for specific year, every year, kindly advise me on how to produce a DAX formula for that.
For info, currently, I'm using a formula such as this-
but it won't work if I filter for different years later.
Hi Anonymous
Try this
Total=
Calculate(sum(table[Amount]),filter(all(table),year(table[Date]) in Allselected(table[date])))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
7 Replies
In your formula you are hardcoding date values. so your mesure will always return total/average for that period only.
You need to update those hardcoded values.
COuld you please share sample data and expected output.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.Thanks for your reply Anonymous.
I'm aware that the current DAX formula is hard-coded & thus, I need to find a new formula to allow for multi-period filtering/selection.
The data wud be as follows:
Jan-17 206176Feb-17 402997Mar-17 634773Apr-17 857848May-17 1170960Jun-17 1406943Jul-17 1637857Aug-17 1909007Sep-17 2128973Oct-17 2379140Nov-17 2574491Dec-17 2911475
Thus, 2017 Cummulative Total Expense will be the sum of all the expenses in 2017 i.e. around 2,911,475.2017 average expense will then be 2,911,475 / 12 = around 242,623.
Kind regards, -Nik
Hi Anonymous
Try this
Total=
Calculate(sum(table[Amount]),filter(all(table),year(table[Date]) in Allselected(table[date])))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.