Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Excel formula in DAX

Hi Experts   Looking at the attached screen output how would you do the following calculation in DAX.   i want to be able to work out H5 = H4*G5 and populate that down in a column in my table. ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    One sample for your reference, please check the following steps as below.

     

    1. Insert an index column in power query.

     

    2. Create a calculated column as below.

    H = 
PRODUCTX(FILTER('Table','Table'[Index] <=EARLIER('Table'[Index])),[1-d/n])

     

MattAllington's avatar
MattAllington
Community Champion
7 years ago

Here is no answer to this question.  DAX formulas always depend on the table structure. You need to learn to layout a visual so that you get visual cues similar to what you are used to in Excel. Have a look at my article -it should  help you with the concept (not the answer - you will need to work through the problem) 

https://exceleratorbi.com.au/compound-growth-using-dax/

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    thanks matt for the heads up, this is tricky as hell. 2 hours down and no solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Matt

       

      if my row where not filter as per pivot table using teh following dax. Should i get a constant answer as opposed to increment of 1 to last row in table i.e. 1, 2 , 3, 4, etc until 2788. i am see 2788 for each row.

      FilteredRows =
VAR LatestYear =
    MAX ( Rates[Year] )
VAR UnfilteredTable =
    ALL ( Rates )
RETURN
    COUNTROWS ( FILTER ( UnfilteredTable, Rates[Year] <= LatestYear ) )