Question on relative time

[jwisrael]

I'm looking to create a custom column based on a date range to show customer activity.

Active = last invoice date within last four months

Active B= last invoice date within last 5-8 months

Active c= last invoice date  within last 9-12 months

Inactive = last invoice date beyond 12months.

I can't seem to figure out the best way to do this, any help or guidance would be appreciated,

Thank you,

[mahoneypat · Accepted solution]

Please try a column expression like this (DAX column, not a query column)

Invoice Status =
VAR vMonthsOld =
    DATEDIFF (
        Table[InvoiceDate],
        TODAY (),
        MONTH
    )
VAR vResult =
    SWITCH (
        TRUE (),
        vMonthsOld <= 4, "Active",
        vMonthsOld <= 8, "Active A",
        vMonthsOld <= 12, "Active B",
        "Inactive"
    )
RETURN
    vResult

Pat

[mahoneypat · Accepted solution]

Yes.  I think you could replace the vMonthsOld variable with

vMonthsOld = CALCULATE(MAX('P21&IBS Data'[Date]), ALLEXCEPT('P21&IBS Data', 'P21&IBS Data'[CustomerName]))

If not, please share some representative mock data for a modified solution.

Pat

[jwisrael]

I eneded up creating another date column for last invocie date and using that in the model you provided. Worked great, plus I can have a column that tells me their exact last invoice dat. I appreciate your help, thank you.

JW

[mahoneypat · Accepted solution]

Please try a column expression like this (DAX column, not a query column)

Invoice Status =
VAR vMonthsOld =
    DATEDIFF (
        Table[InvoiceDate],
        TODAY (),
        MONTH
    )
VAR vResult =
    SWITCH (
        TRUE (),
        vMonthsOld <= 4, "Active",
        vMonthsOld <= 8, "Active A",
        vMonthsOld <= 12, "Active B",
        "Inactive"
    )
RETURN
    vResult

Pat

[jwisrael]

Thank you! This worked great, however, because my data goes line by line with invoices, this just bases it on where the date falls with that specific invoice. Is there a way to highlight the relative time from last invoice date by customer name within the expression you sent? I tried adding this into the expression, 

Last Customer Invoice Date = CALCULATE(LASTDATE('P21&IBS Data'[Date]), ALLEXCEPT('P21&IBS Data','P21&IBS Data'[CustomerName])).