PowerPvot Sales Without Gap

[m1nkeh]

Hi,

Trying to calc sales in the last 12 months "without gap", as in... if customer purchased one item in each of Jan, Feb, and Apr .. this measure would show a value of 1... (here my screenshot shows a value of 2 in Apr, but you get the idea)

So, i guess it should be something like this:

SalesNoBreak:=CALCULATE (
    [Sales],
    DATESBETWEEN (
        Dates[Date],
        DATE(2015, 03, 01),
        LASTDATE ( Dates[Date] )
    )
)

But i can't for the life of me how to work out how to "automate" the DATE(2015, 03, 01) portion...

Ty.

 

[scottsen]

I'm not gonna lie... this one seems pretty tricky

I do think you are on the right track. Where you have DATE(2015, 03, 01)... that needs to be a calculation for "after the most recent month that had 0 sales"... for which, I bet you end up using LASTNONBLANK()

 

[m1nkeh]

looked at this for about 4 hours this morning, and have little more than simply knowing that i need to work first of the month in which the customer had no sales (EOMONTH() -1 or some variant), but i still cant get "LASTDATE() except for the actual last sale"... maybe i should mark it as a calc column in the table?? also makes me wonder what should happen when looking at all customers (i.e. not-filtered)

arghhh, thought of another issue now as when work out the last 12 months sales if there are no gaps, but that can probs be achieved with a simple IF()

 

[scottsen]

I'd play around with a measure like this:
Last Month with No Sales :=LASTNONBLANK(Calendar[Month], IF ([Sales] = BLANK(), 1))