Forum Discussion

syedsiraj10's avatar
syedsiraj10
Regular Visitor
8 years ago
Solved

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 value...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • syedsiraj10's avatar
    syedsiraj10
    8 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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,
Lydia

syedsiraj10's avatar
syedsiraj10
Regular Visitor
8 years ago

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.