getting total or average for a specific year

[Anonymous]

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-

Total.OPEX.2017 =

CALCULATE([Total.Operating.Expenses],DATESBETWEEN('calendar'[Date],"01/01/2017","31/12/2017"))

but it won't work if I filter for different years later.

Kind regards, -Nik

[Anonymous]

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.

[Anonymous]

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   206176

Feb-17  402997

Mar-17  634773

Apr-17  857848

May-17  1170960

Jun-17  1406943

Jul-17  1637857

Aug-17  1909007

Sep-17  2128973

Oct-17  2379140

Nov-17  2574491

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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 is

Total OPEX = [60000-Supplies] + [61000-Manpower] + [62000-Advertising.Promotion] + [63000-Admin.Expenses] + [64000-Repairs.Maintenance] + [69000-Other.OPEX]

date table is named 'calendar'[Date].

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

many tks, @Anonymous.

krgds, -nik