Forum Discussion
Calculate average quarterly wise without considering current year data
I have a requirment to calculate average quartely wise data without considering current year data.
I have created a created a formuale to calculate the average , but its conisdering all the values.
Average For Quarters = DIVIDE(Query1[Column1],Query1[Quarter Calculation])
How we can exclude the current year values from the calculation. Similarly i need to calculate avergage monthly
without considering current year data.
Thank for the reply Anonymous.
I was able to solve the problem by creating a variable
Forecast = IF('Cash Flow'[FY_DC]='Cash Flow'[CurrentYear],0,'Cash Flow'[Average]).
My requirment was to show Average Quarterly wise across the years. For each quarter I had to show the forecast with Average() we will get the average for entire dataset in the visualization.
3 Replies
Tough to say exactly without sample data but somewhere in your calculation you probably need to use a CALCULATE and FILTER to filter out the current year. [Year] = YEAR(TODAY())
syedsiraj10,
Do you get expected result when you create another measure using DAX below. You may need to create year column in the Query1 table.
Measurewithoutcurrentyear = CALCULATE([Average For Quarters], FILTER(Query1,Query1[Year]<YEAR(TODAY())))
There is also an example for your reference.
Measurewithoutcurrentyear = CALCULATE(SUM(Sheet5[SalesAmount]), FILTER(Sheet5,Sheet5[Year]<YEAR(TODAY())))
Regards,
LydiaThank for the reply Anonymous.
I was able to solve the problem by creating a variable
Forecast = IF('Cash Flow'[FY_DC]='Cash Flow'[CurrentYear],0,'Cash Flow'[Average]).
My requirment was to show Average Quarterly wise across the years. For each quarter I had to show the forecast with Average() we will get the average for entire dataset in the visualization.
