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.
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.sorry for the late reply, Anonymous.
let's start all over again regarding this.
actually, i'm developing multiple dax calculations. for instance, for expenses i started the total/summation of each individual expense items with a dax calculation such as the one below for supply expenses:
60000-Supply =
CALCULATE (
SUM ( 'fin'[Amount] ),
'coa'[Account_Group_Code] = 60000
)the summation for other expense items (e.g. Manpower / AdvertisingPromo) will also be using similar way of dax calculation.
subsequently, i'll sum up the cummulative total for each expense items through a dax formula as follows:
60000-Supply .CT =
IF (
COUNTROWS ( 'fin' ),
TOTALYTD ( [60000-Supply], 'cldr'[Date] )
)them i'll sum up all the expense accounts cummultive totals (CT) using a simple dax formula such as the following:
Total Opex CT = [60000-Supply.CT] + [61000-Manpower.CT] + [62000-AdvertisingPromo.CT] + [69000-Other Opex.CT]now, i need to get a constant monthly average expense for each year where the cummalative total of each year will be divided by 12 (for 12 months in a calendar year). for example, the monthly average expenses for 2017, 2018, 2019 will constantly be $1500, $2000, and $1700 respectively. i may use the constant monthly average expense for other (KPI ratio) calculations in each of those years.
in summary & to recap, i need to know how to calculate a constant monthly average for each individual years.
a sample dataset for thsi can be found here.
tks & krgds, -nik