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.
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