DAX Time Intelligence with Conditional Logic

[aeid101]

 I have a "sales" table with team ID, payment type and date (see example below):

I want to write a DAX formula that will "lookup" the prior month's sales amount for a "Monthly" team and the 3 month prior sales amount for a "Quarterly" team. The lookup also needs to be filtered by team ID, so the prior sales belong to the same team.

Here's an example of the desired output (Change in Sales) :

PriorMonthSales =CALCULATE(
SUM(Table[Sales]),
FILTER(
   Table[TeamID]&&
   Table[Date] - 1
 )

PriorQuarterSales =CALCULATE(
SUM(Table[Sales]),
FILTER(
   Table[TeamID]&&
   Table[Date] - 3
 )

PriorSales = IF(Table[PaymentType] = "Monthly",PriorMonthSales,   PriorQuarterSales)

These formulas obviously don't produce the desired result, but I am sharing them here to show my approach to solving this. Perhaps, someone more knowledgeable here can assist with the correct syntax and logic.

Thanks!

[v-qiuyu-msft]

Hi @aeid101,

In your scenario, please follow steps below to calculate the difference for sales within each Team ID instance:

1. Return previous date within each Team ID group instance.

2. Return previous sales within each Team ID group instance.

3. Calculate teh difference between each row within each Team ID instance.

Reference:

PowerPivot DAX Session Notes 2 – Previous Row

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[achinm45]

Hi @aeid101

Try this attached screenshot

PreviousMonthAmount

[MattAllington]

Do you have a calendar table?  It doesn't look like it.  I suggest you read my article about calendar tables here. 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

It looks like a monthly calendar table will work for you. Make sure you have an ID column and then you can write your filter function to operate on the ID column. 

Post back if that doesn't make sense