Previous month not equal to 0, current month equal to 0 column or measure to use as filter

[Hayleysea]

Hi there,

I have a table which has a date column with data over several years. I need a measure or column to indicate the following:

- The previous month to today entered value <> 0 (column called actuals) - so they entered a value

- The current month has entered value = 0 or Blank (same column called actuals) - so no value has been entered

There are several lines for one project so it needs to check that the sum of all project ID's for the month is or is not = 0 for the current and previous months.

Ultimately this will be used as a filter in a table to call out the ones that entered values last month but haven't yet this month.

Thankyou!!

[Anonymous · Accepted solution]

Hi @Hayleysea -

In cases like this I prefer to use a Date table with Relative Months. There are many ways to create a date dimension, one describing relative dates is here. I've also attached an alternative.

Once you have your date dimension with relative dates, you can create a measure like the following.

Notes:

1. This can be extended to apply to any month by using "MAX('Date'[Relative Month])" instead of 0.
2. This could also be extended to classify Churn, New, Returning, Existing.

Actual Churn This Month = 
var _MonthToAnalyze = 0 --MAX('Date'[Relative Month])
var _CurrentMonthActuals =
    CALCULATE(
        [Sales],
        'Date'[Relative Month] = _MonthToAnalyze
    ) + 0
var _PreviousMonthActuals =
    CALCULATE(
        [Sales],
        'Date'[Relative Month] = _MonthToAnalyze - 1
    ) + 0
return 
    AND(
        _PreviousMonthActuals <> 0,
        _CurrentMonthActuals = 0
    )

You can use this measure as a filter to only keep True values.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.