locked
Cumulative Running Total fails in DAX Expression..! RRS feed

  • Question

  • Hi,

        I need a running total for calculated measures in the boundaries of year.

    Say for example if i select the date as 20 June 2015, then it should show the total count starting from 1st jan 2015 to 20th june 2015.

    I wrote below two queries but all success with no error, but it returns only the selected date count value but not up to year starting. Please suggest 

    YTD Count:=TOTALYTD(COUNTAX(FILTER('Vehicle',[Vehicle Status]="REJECTED"),[ID]),Time[FullDateAlternateKey])

    YTD Count2:=Calculate(Sum([ID]),Time[FullDateAlternateKey])


    Thanks, Avula.GK www.solverminds.com

    Thursday, June 25, 2015 3:15 PM

Answers

  • Hi Avula,
    Darren is right in that the running total you're looking for is a standard DAX function. The problem is not in the year-to-date calculation, but in your count function. FILTER is a table function and cannot be used in a COUNTA, as you have noticed.
    I propose one of the two calculations below:

    YTD Count:= TOTALYTD(COUNTROWS(FILTER('Vehicle',[Vehicle Status]="REJECTED")),Time[FullDateAlternateKey])

    YTD Count 2:=TOTALYTD(COUNTA('Vehicle'[ID]),Time[FullDateAlternateKey],'Vehicle'[Vehicle Status]="REJECTED")

    The latter one is better because it doesn't use a table function. Good to know that the TOTALYTD function can have an additional filter as the third argument.


    Regards, Michiel

    • Proposed as answer by Charlie Liao Wednesday, July 8, 2015 1:08 AM
    • Marked as answer by Michael Amadi Thursday, January 14, 2016 6:32 AM
    Tuesday, June 30, 2015 9:12 AM
    Answerer

All replies

  • Hi,

        I need a running total for calculated measures in the boundaries of year.

    Say for example if i select the date as 20 June 2015, then it should show the total count starting from 1st jan 2015 to 20th june 2015.

    I wrote below two queries but all success with no error, but it returns only the selected date count value but not up to year starting. Please suggest 

    YTD Count:=TOTALYTD(COUNTAX(FILTER('Vehicle',[Vehicle Status]="REJECTED"),[ID]),Time[FullDateAlternateKey])

    YTD Count2:=Calculate(Sum([ID]),Time[FullDateAlternateKey])


    Thanks, Avula.GK www.solverminds.com

    • Merged by Charlie Liao Monday, June 29, 2015 6:11 AM duplicate thread
    Thursday, June 25, 2015 8:30 PM
  • Why are you using the "X" version of COUNTA? It does not seem like you need it since you appear to be just passing in a column reference. I would have thought that the following should work

    YTD Count:=TOTALYTD(COUNTA(FILTER('Vehicle',[Vehicle Status]="REJECTED")),Time[FullDateAlternateKey])


    http://darren.gosbell.com - please mark correct answers

    Thursday, June 25, 2015 9:18 PM
  • Hi Avula,

    According to your description, you need to create a measure to return the running total, right?

    Running Total (whether sum, average or any other aggregate) is one of the most common calculations used when analyzing business as it offers insights in relation to data trends. The DAX expression looks like:
    ALCULATE(
        SUM( FactMonthlySales[Sales Amount] ),
        FILTER(
            ALLEXCEPT(
                FactMonthlySales,
                FactMonthlySales[Calendar Year]
            ) ,
        FactMonthlySales[Month] <= EARLIER( FactMonthlySales[Month] )
        )
    )

    Here are some articles that describe some of the most common scenarios when developing running total calculations. Please refer to the links below.
    https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/
    http://www.daxpatterns.com/cumulative-total/

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, June 26, 2015 2:55 AM
  • Hi Darren,

    It doesn't work. Actually CountA requries only column refernce.

    I tried another using CountX , it also failed , it gives only single day count.

    YTD Count:=TOTALYTD(COUNTA(FILTER('Vehicle',[Vehicle Status]="REJECTED")),Time[FullDateAlternateKey])

    YTD Count:=TOTALYTD(COUNTX(FILTER('Vehicle',[Vehicle Status]="REJECTED"),[RejectedID]),Time[FullDateAlternateKey])


    Thanks, Avula.GK www.solverminds.com

    Friday, June 26, 2015 3:58 AM
  • Hi Charlie 

       Thanks for your reply, 

    But those gives us cumulative for entire years, but not for particular year. It works if i give the year hirarchy in the Row wise as show below. But what if i dont give that. As my reqriment purly based from starting of the year to selected date. Means year boundraies must be applied.

    CB:=[CBSUM](DATESBETWEEN('Date'[FullDateAlternateKey], BLANK(),LASTDATE( 'Date'[FullDateAlternateKey])), All('Date'))

    My requiremt Row label doen't come into picture.

    

      

        


    Thanks, Avula.GK www.solverminds.com

    Friday, June 26, 2015 6:18 AM
  • Hi Avula,
    Darren is right in that the running total you're looking for is a standard DAX function. The problem is not in the year-to-date calculation, but in your count function. FILTER is a table function and cannot be used in a COUNTA, as you have noticed.
    I propose one of the two calculations below:

    YTD Count:= TOTALYTD(COUNTROWS(FILTER('Vehicle',[Vehicle Status]="REJECTED")),Time[FullDateAlternateKey])

    YTD Count 2:=TOTALYTD(COUNTA('Vehicle'[ID]),Time[FullDateAlternateKey],'Vehicle'[Vehicle Status]="REJECTED")

    The latter one is better because it doesn't use a table function. Good to know that the TOTALYTD function can have an additional filter as the third argument.


    Regards, Michiel

    • Proposed as answer by Charlie Liao Wednesday, July 8, 2015 1:08 AM
    • Marked as answer by Michael Amadi Thursday, January 14, 2016 6:32 AM
    Tuesday, June 30, 2015 9:12 AM
    Answerer