DAX Time Intelligence with Conditional Logic

Question

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!

WimV WimV · Accepted Answer · 2016-05-09T12:46:52

To calculate the total of the previous month/quarter you can add a calculated column. However you need to change your month column to a date. jan 16 => 1/1/2016

formula:

=
IF (
    myTable[paymenttype] = "monthly",
    CALCULATE (
        SUM ( myTable[sales] ),
        FILTER (
            myTable,
            myTable[TeamID] = EARLIER ( myTable[TeamID] )
                && DATEADD ( myTable[date], 1, MONTH ) = EARLIER ( myTable[date] )
                && myTable[paymenttype] = EARLIER ( myTable[paymenttype] )
        )
    ),
    CALCULATE (
        SUM ( myTable[sales] ),
        FILTER (
            myTable,
            myTable[TeamID] = EARLIER ( myTable[TeamID] )
                && DATEADD ( myTable[date], 3, MONTH ) = EARLIER ( myTable[date] )
                && myTable[paymenttype] = EARLIER ( myTable[paymenttype] )
        )
    )
)

DAX Time Intelligence with Conditional Logic

1 Answers