Need help with Cumulative (Running) Total

[Roseventura]

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.

[Dog]

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]

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 , but that's my date table.)

Rose

[v-sihou-msft]

@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]

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