I’ve trying to resolve this by myself, but I’m still struggling with the formula that I need.
I have created the measure named Purchased in by Current Date using this formula:
Purchased In by Current Date = // Number of months that each particular client purchased within the date selected.
VAR MonthlySales = SUMMARIZE( 'SALES DATA', 'CALENDAR'[Month & Year],
"SalesAmt", [Total Net Sales] )
CALCULATE( DISTINCTCOUNT( 'CALENDAR'[Month & Year] ),
[SalesAmt] > 0 ) )
This measure is giving me the total number of months a customer is purchasing within the period I’m selecting. In this following example, I’m selecting Oct 2017 where customer 312 bought that same period.
After verifying in my excel file, everything works fine up to here…
The next step is to make the same calculation, but instead the current period I’m selecting, I need the calculation going back 12 months. For this, I created the following measure:
Months Purchased In 12 months back = // Number of months that each particular client purchased in within the last 12 months from the date selected.
VAR MonthlySales =
SUMMARIZE( 'CALENDAR', 'CALENDAR'[Month & Year], "SalesAmt", [Total Net Sales] ),
DATESINPERIOD( 'CALENDAR'[Date], MIN( 'CALENDAR'[Date] ), -1, YEAR ) )
FILTER( MonthlySales, [SalesAmt] > 0 ) )
In this case, using the same customer as an example, the result now is showing 8 months.
After reviewing my excel file, I noted that there is one month that is not being included in the calculation, since the correct total should be 9 istead of the 8 months I’m getting.
Could you give me any idea about what is wrong with the formula I did? I would also need help about how to replace the blanks for zeros.
Thanks in advance for your help!