locked
Running Product using DAX RRS feed

  • Question

  • Hi All,

    I tried below measure to get the YTD running product and its working fine for me. but I found one issue that its give me slow performance while I see running product by different dimension level. its very strange for me now, could please help me on that or give me the advice on it.

    YTD Return:= IF(HASONEVALUE(Calender[Year])),

                               POWER(10,SUMX(

                                                        FILTER(ALL(Calendar), Calender[Year] = VALUES(Calender[Year]) && Calendar[Date] <= MAX(Calendar[Date])), IF([Return (%)] > -1, LOG(1+[Return (%)])

              ), -1

    

    Friday, August 14, 2015 10:10 AM

Answers

  • I'd actually adjust my previous answer to this for more generally applicable measures:
    CurrencyAdj:=IF(
      HASONEVALUE(CurrencyRate[CurrencyCode])
      ,SUMX(
        Calendar
        ,CALCULATE(SUM('Transaction'[Amount])) * CALCULATE(MAX(CurrencyRate[CurrencyRate]))
      )
      ,BLANK()
    )
    
    
    RunningTotalCurrencyAdj:=SUMX(
      FILTER(
        ALL(Calendar[Date])
        ,Calendar[Date] <= MAX(Calendar[Date])
      )
      ,[CurrencyAdj]
    )

    This depends on some filter existing (e.g. a slicer) on CurrencyRate[CurrencyCode] to ensure that the measure doesn't return nonsense (it makes no sense to have >=2 currencies represented by the same value - this will work just fine if you want to plot multiple currencies or use currency on a pivot table as a row/column filter).

    [CurrencyAdj] will give you a currency adjusted amount for whatever time frame is in context, and works appropriately at coarser levels of granularity (e.g. for a month it gives you the sum of adjusted values for each day in the month).

    [RunningTotalCurrencyAdj] provides a simple running total of [CurrencyAdj] for all days on or before the current date in context.

    GNet Group BI Consultant

    • Proposed as answer by greggyb Wednesday, September 16, 2015 8:52 PM
    • Marked as answer by Michael Amadi Friday, September 25, 2015 5:53 AM
    Wednesday, September 16, 2015 8:37 PM

All replies

  • This is the standard pattern for running totals

    http://www.daxpatterns.com/cumulative-total/

    IF is VERY expensive in DAX so that's likely a major cause of slow performance.

    Friday, August 14, 2015 12:07 PM
  • Hi,

    You have errors in your formula. It cannot work fine :)

    You can post only SUMX part because thats the part which is slowing it down.

    I think you can rewrite your query using CALCULATE instead of SUMX.

    SUMX(filter,measure)

    CALCULATE(measure,filter) in your case


    Friday, August 14, 2015 12:12 PM
  • Hi Steelleg4,

    Thanks for your fast reply, As you said that I need to use CALCULATE(measure,filter) instead of SUMX(filter,measure)..

    so I tried below measure in adventure database's tables Factinternetsales and dim date table:

    Corrected measure using SUMX:

    YTD Return using SUMX:=IF(HASONEVALUE(DimDate[CalendarYear]),
                               POWER(10,SUMX(
                                                        FILTER(ALL(DimDate), DimDate[CalendarYear] = VALUES(DimDate[CalendarYear]) && DimDate[DateKey] <= MAX(DimDate[DateKey])), 
                                             LOG(1+[Return])
                         ))) -1

    Use Calculate instead of SUMX:

    YTD Return using calculate:=IF(HASONEVALUE(DimDate[CalendarYear]),
                               POWER(10,CALCULATE(LOG(1+[Return]),
                                                        FILTER(ALL(DimDate), DimDate[CalendarYear] = VALUES(DimDate[CalendarYear]) && DimDate[DateKey] <= MAX(DimDate[DateKey]))
                         ))) -1

    The above measure ([YTD Return using calculate]) not giving me as expected running product result, If I am doing something wrong please correct it and share with me. 

    Thanks in advance.

    Friday, August 14, 2015 4:04 PM
  • Try turn LOG(1+[Return]) into calculated column.

    And then make calculated measure.

    YTD Return using calculate :=
    CALCULATE (
        SUM ( [CalculatedColumn] ),
        FILTER (
            ALL ( DimDate ),
            DimDate[CalendarYear] = MAX ( DimDate[CalendarYear] )
                && DimDate[DateKey] <= MAX ( DimDate[DateKey] )
        )
    )
    Friday, August 14, 2015 6:41 PM
  • Your measure has a few items in it which will affect performance. I'll address these individually.

    The first is the SUMX(), which forces evaluation to the formula engine (single threaded) and is a row-by-row iterator in DAX. This will always be a performance hit, as Stelleg4 has pointed out.

    The second is the FILTER() on your date dimension. FILTER() is also a formula engine row-by-row iterator. The first piece of advice I can give you is to check the size of your date dimension. Make sure you only have dates for the years that your fact tables cover. I.e. don't have a date table going to 2050 if your data only runs to the end of the current year and don't start your date table in 1950 if you're only looking at the last five years.

    The third item is the IF() within the SUMX(). IF() also forces formula engine evaluation, and in this case you are calling it for every row in the table returned by FILTER() in the first argument to SUMX(). (The outer IF() isn't much of a concern in this construction.)

    Check the size of your date dimension and see if that helps your performance, then try the following:
    YTD Return:=
    IF(
        HASONEVALUE(Calender[Year])
        ,POWER(
            10
            ,SUMX(
                FILTER(
                    ALLEXCEPT(Calendar, Calendar[Year])
                    ,Calendar[Date] <= MAX(Calendar[Date])
                )
                ,IF(
                    [Return (%)] > -1
                    ,LOG(1 + [Return (%)])
                    ,-1
                )
            )
        )
        ,BLANK()
    )
    The other items to check are the logic behind your [Return (%)] measure, as that will be calculated once per row returned by FILTER(), and also to check if you can rewrite [Return (%)] to remove the need for your IF() as the second argument to SUMX().


    GNet Group BI Consultant

    Tuesday, August 18, 2015 3:13 PM
  • Hi Greg,

    Thanks a lot, Now the 50% performance is improved. and I want to improve another 50% and I found that one of the measure gives me the slow performance, acutally I m calculating the running total using below measure and then apply currency conversion rate from currency table.

    Running total:= Calculate(SUM([Amount]),

                                       FIlter(AllNOBlankROW(Calendar), Calendar[Date]<= MAX(Calendar[Date])))

    Running Total USD:= SUMX(

                                               Filter(All(Calendar), Calendar[Date] = MAX(Calendar[Date])),

                                                 [Running Total] * [USD Currency]

                                              )

    Note: Calendar table have 5 years of data and transaction data have 1 million (contain missing dates means no transaction for missing dates).

    Please help me out.

    Thanks in advance.


    • Edited by ManikantM Friday, August 21, 2015 12:51 PM
    Friday, August 21, 2015 12:47 PM
  • Your [Running Total USD] is either copied incorrectly here or basically an expensive no-op.

    You're calling a FILTER() that can never return more than a single row from Calendar. Rip our the SUMX() and the FILTER() and you should get the same thing back.

    Additionally, I can't tell you if your [USD Currency] is a performance bottleneck without seeing the measure, but it is another potential source of performance issues if it's poorly defined.

    GNet Group BI Consultant

    Thursday, August 27, 2015 7:29 PM
  • Thanks Greg,

    Yes, you r right, the [Running Total USD] measure is incorrect, below here the correct measure:

    [Running total USD]:= SUMX (Generate(Calculatetable(Summarize('Calendar', 'Calendar'[Date]),Filter(All(Calendar),Calendar[Date] = MAX(Calendar[Date]))),Calculatetable(Values([USD Currency]),ALL(Calendar))),

    Calculate([USD Rate],Filter('Currency','Currency'[Date] = Calendar[Date])) * [Running Total]

    )

    Note: Transaction table contain missing dates and [Running Total] gives the cummulative sum day basis.

    Or do you have any other solution please suggest me, below here is my table structure:

    Transaction table: Amounts are available into INR currency.

    Order Date Order id Amount
    1/1/2015 1 1000
    4/1/2015 2 2000
    7/1/2015 3 100
    10/1/2015 4 3000
    14/1/2015 5 200

    Standard Calendar table:

    Currency rate table:

    Currency Date Currency code Currency Rate
    1/1/2015 USD 16.4
    2/1/2015 USD 15.4
    3/1/2015 USD 16.0
    4/1/2015 USD 15.5
    5/1/2015 USD 16.1

    and so on....

    want to calculate the running total daily basis into INR and USD currency...and transaction table contains 1 millions of records.

    Thanks in advance.

    Friday, September 4, 2015 5:07 AM
  • RunningTotalUSD:=SUMX(
      FILTER(
        ALL(Calendar[Date])
        ,Calendar[Date] <= MAX(Calendar[Date])
      )
      ,CALCULATE(SUM('Transaction'[Amount]))
        * LOOKUPVALUE(
              CurrencyRate[CurrencyRate]
              ,CurrencyRate[CurrencyDate], CALCULATE(VALUES(Calendar[Date]))
              ,CurrencyRate[CurrencyCode], "USD"
            )
    )
    You cannot just multiply your [RunningTotal] by a conversion rate. On any given date, [RunningTotal] returns a single value made up of amounts from multiple dates (each with potentially distinct conversion rates). The multiplication has to happen within the SUMX() of [RunningTotal], as I've shown above. With this technique you'll have to write a different measure for every currency you might be seeing. This is not ideal if you have more than a handful of currencies you're interested in.

    As an alternative, you could write a slightly modified measure that will work with a CurrencyCode slicer:
    RunningTotalCurrencyCodeSlicer:=SUMX(
      FILTER(
        ALL(Calendar[Date])
        ,Calendar[Date] <= MAX(Calendar[Date])
      )
      ,IF(
        HASONEVALUE(CurrencyRate[CurrencyCode])
        ,CALCULATE(SUM('Transaction'[Amount]))
          * CALCULATE(MAX(CurrencyRate[CurrencyRate]))
        ,BLANK()
      )
    )
    This will only work if you have an entry in CurrencyRate for every date.

    Here is the model, for reference:




    GNet Group BI Consultant

    Wednesday, September 16, 2015 1:35 PM
  • I'd actually adjust my previous answer to this for more generally applicable measures:
    CurrencyAdj:=IF(
      HASONEVALUE(CurrencyRate[CurrencyCode])
      ,SUMX(
        Calendar
        ,CALCULATE(SUM('Transaction'[Amount])) * CALCULATE(MAX(CurrencyRate[CurrencyRate]))
      )
      ,BLANK()
    )
    
    
    RunningTotalCurrencyAdj:=SUMX(
      FILTER(
        ALL(Calendar[Date])
        ,Calendar[Date] <= MAX(Calendar[Date])
      )
      ,[CurrencyAdj]
    )

    This depends on some filter existing (e.g. a slicer) on CurrencyRate[CurrencyCode] to ensure that the measure doesn't return nonsense (it makes no sense to have >=2 currencies represented by the same value - this will work just fine if you want to plot multiple currencies or use currency on a pivot table as a row/column filter).

    [CurrencyAdj] will give you a currency adjusted amount for whatever time frame is in context, and works appropriately at coarser levels of granularity (e.g. for a month it gives you the sum of adjusted values for each day in the month).

    [RunningTotalCurrencyAdj] provides a simple running total of [CurrencyAdj] for all days on or before the current date in context.

    GNet Group BI Consultant

    • Proposed as answer by greggyb Wednesday, September 16, 2015 8:52 PM
    • Marked as answer by Michael Amadi Friday, September 25, 2015 5:53 AM
    Wednesday, September 16, 2015 8:37 PM