Forum Discussion

jwisrael's avatar
jwisrael
Frequent Visitor
5 years ago
Solved

Question on relative time

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 ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
    mahoneypat
    5 years ago

    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

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
    jwisrael
    Frequent Visitor
    5 years ago

    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