Forum Discussion
getting total or average for a specific year
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.
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:
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.many tks, Anonymous.
krgds, -nik
hi again Anonymous ,
if i have operating expenses items that have been summed up as one via dax formula/measure and called total opex, how will your suggested dax formula be written?krgds, -nik
p.s.
the total opex formula isTotal OPEX = [60000-Supplies] + [61000-Manpower] + [62000-Advertising.Promotion] + [63000-Admin.Expenses] + [64000-Repairs.Maintenance] + [69000-Other.OPEX]
date table is named 'calendar'[Date].
are these column names?
what do you want column or measure.
Share sample data 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.