Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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-
Solved! Go to 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.
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:
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.
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].
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
many tks, @Anonymous.
krgds, -nik
User | Count |
---|---|
106 | |
90 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
101 | |
96 | |
74 | |
67 |