Forum Discussion

Olia's avatar
Olia
Icon for Advocate II rankAdvocate II
7 years ago
Solved

Rolling average with conditions

Folks, could you please assist me with this?

 

I have very messy data, which contains 3 columns:

1) FA

2) VERSION

3) SUM(AMOUNT)

4) FSI (=financial statement item number)

 

what makes things so hard are the following rules:

 

If FA is an integer (a number), then SUM(AMOUNT) is $$$

If FA is a string (some words), then SUM(AMOUNT) is fte's (so it refers to people, and not $$$)

 

If VERSION = CCA, then SUM(AMOUNT) is refering to Actual numbers. IF VERSION is everything else, then SUM(AMOUNT) refers to Budget / Forecast / Rolling forecast / actuals from a different system / anything else but Actuals.

 

I need to calculate the 6 months Rolling Average for Actual $$$ (so, FA = a number & VERSION = CCA). In addition, the rolling average needs to be per FSI.

I do have a DATE calculated measure: DATE = date([fiscal_year],[fiscal_month],01)

 

 

I'm rather noob and don't understand DAX good enough yet to figure this one out. (so suggestions on where to learn DAX are also most welcome). Halp plz?

 

 

edit: Just made a calculated measure called CCA_$. would that help?

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Olia,

    Based on my test, you could refer to below formula to calculate a table meets the requirements that 'FA is a number' and VERSION=CCA':

    Table = CALCULATETABLE('Sheet2',FILTER(Sheet2,'Sheet2'[VERSION]="CCA"&&UNICODE('Sheet2'[FA])<65))

    Result:

    But I could not understand how do you want to calculate the average Actual $$$ per FSI? Could you please offer me more information if possible? You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

     

3 Replies