Forum Discussion

Roseventura's avatar
Roseventura
Icon for Responsive Resident rankResponsive Resident
9 years ago

Need help with Cumulative (Running) Total

Help!

 

I'm trying to get my Cumulative LY total to work, but all I get is a YTD grand total.  I created 2 measures:  Cumulative TY and Cumulative LY.  The Cumulative TY works, but the LY one doesn't.  The measures identical except for the measure they reference (in bold below), so I'm not seeing what I'm doing wrong.  The illustration below includes the TY YTD Sales and LY YTD Sales so you can see that my YTD calculations are correct.

 

Cumulative measures:

 

Cumulative TY =

     CALCULATE('PBI_Invoice_Multi'[TY YTD Sales],

     FILTER(ALL(PBI_Invoice_Multi),PBI_Invoice_Multi[Formatted Inv Date]<=MAX(PBI_Invoice_Multi[Formatted Inv Date])))

 

Cumulative LY =
     CALCULATE('PBI_Invoice_Multi'[LY YTD Sales],
     FILTER(ALL(PBI_Invoice_Multi),PBI_Invoice_Multi[Formatted Inv Date]<=MAX(PBI_Invoice_Multi[Formatted Inv Date])))

 

As you can see both measures are identical except for the measure they reference in the CALCULATE part of the formula.

 

 

 

Need Help

4 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Roseventura

     

    Are TY YTD Sales and LY YTD Sales measures? If they are columns, you supposes to have aggregation function in CALCULATE().

     

    Can you post the formula of LY YTD Sales? If possible, please share your .pbix file.

     

    Regards,

    • Roseventura's avatar
      Roseventura
      Icon for Responsive Resident rankResponsive Resident
      9 years ago

      Simon:

       

      Here are the measures (they are not columns):

       

      LY YTD Sales = calculate(sum(PBI_Invoice_Multi[Ext Sales Amount]),
          filter(PBI_Invoice_Multi,PBI_Invoice_Multi[Formatted Inv Date]<=PBI_Invoice_Multi[TodayLastYear]))

       

      TY YTD Sales = calculate(sum(PBI_Invoice_Multi[Ext Sales Amount]),filter(PBI_Invoice_Multi,PBI_Invoice_Multi[Year]=Year(PBI_FSCAPF[Current Date])))

       

      Thanks,

      Rose

       

  • Dog's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    Hi,

    looking at your initial measure I imagine that it's trying to sum sales within the LY YTD measure that occured before the end of each month in the current year which would always be the end of year total.

    do you have a dates table? this would make this a lot easier I think.

    not sure if it helps but this is how I usually do things.

    Create a measure that just totals the sales
    SumOfSales:=sum(PBI_Invoice_Multi[Ext Sales Amount])

    I'd expect this to be filtered within the visual of the PBI report page but your main sales should be as straight forward as the following .
    This Year Sales:=Calculate([SumOfSales],FILTER(Dates, Dates[Year] = YEAR(TODAY())))

    create a new measure for the running total.
    Cumulative This YTD Sales:=CALCULATE([SumOfSales], ALL(Dates[DateKey]), datesytd(Dates[DateKey], "12/31")

    then use the previous measures to get the previous year information
    Last Year Sales:=CALCULATE([This Year Sales], SAMEPERIODLASTYEAR(Dates[DateKey]))
    Cumulative Last Year YTD Sales:=CALCULATE([Cumulative This YTD Sales], SAMEPERIODLASTYEAR(Dates[DateKey]))


    not sure if this helps any...

    • Roseventura's avatar
      Roseventura
      Icon for Responsive Resident rankResponsive Resident
      9 years ago

      Dog,

       

      I will digest what you said, give it a try and let you know if that worked. 

       

      Yes, I do use a date table.  That's the table (that's shown in measure) called PBI_FSCAPF.  (I didn't name it, someone else did :womanfrustrated:, but that's my date table.)

       

      Rose