Is it possible to find the period between months with negative values by using datediff?

[Fighting21]

I have measure _CurrentDateValueMPreviousDateValue and i want find the period between months with minus _CurrentDateValueMPreviousDateValue.

https://drive.google.com/file/d/1CgDLgj0NzLY384LBMkJr5cGmCnAkbgIQ/view?usp=sharing 

[Anonymous · Accepted solution]

Hi @Fighting21 

The calculation of measures is dynamic and dependent on the current context, using measures to achieve your desired effect can be challenging.
Please consider using calculated columns to do it.

1. Create a column to calculate the difference between the current row and the previous row:

Difference = 
VAR PrevDate = 
    CALCULATE(
        MAX('DataTable'[Date]),
        FILTER(
            'DataTable',
            'DataTable'[Category] = EARLIER('DataTable'[Category]) &&
            'DataTable'[SubCategory] = EARLIER('DataTable'[SubCategory]) &&
            'DataTable'[Date] < EARLIER('DataTable'[Date])
        )
    )
VAR PrevValue = 
    CALCULATE(
        MAX('DataTable'[Value]),
        FILTER(
            'DataTable',
            'DataTable'[Category] = EARLIER('DataTable'[Category]) &&
            'DataTable'[SubCategory] = EARLIER('DataTable'[SubCategory]) &&
            'DataTable'[Date] = PrevDate
        )
    )
RETURN
IF(ISBLANK(PrevValue), 'DataTable'[Value], 'DataTable'[Value] - PrevValue)

2. Create a column to calculate the month interval between negative values:

MonthsBetweenNegativeValues = 
VAR CurrentDate = 'DataTable'[Date]
VAR CurrentCategory = 'DataTable'[Category]
VAR CurrentSubCategory = 'DataTable'[SubCategory]
VAR PreviousNegativeDate = 
    CALCULATE(
        MAX('DataTable'[Date]),
        FILTER(
            'DataTable',
            'DataTable'[Category] = CurrentCategory &&
            'DataTable'[SubCategory] = CurrentSubCategory &&
            'DataTable'[Date] < CurrentDate &&
            'DataTable'[Difference] < 0
        )
    )
RETURN
IF(
    'DataTable'[Difference] < 0,
    IF(
        ISBLANK(PreviousNegativeDate),
        BLANK(),
        DATEDIFF(PreviousNegativeDate, CurrentDate, MONTH)
    ),
    BLANK()
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fighting21]

Thank you very much, sorry for accept it as the solution lately.

[PhilipTreacy]

Hi @Fighting21 

I don't know what you mean by 'month between'.  Please show an example of the result you want, including the sub-categories you've mentioned.

Regards

Phil

[Fighting21]

Thank you for reply i upgraded my question.