Generate values for previous row and current one in a calcalated column

[Phuonghm22]

Hi guys,
I have a problem with this situation.

I have column A and I want to create column B using DAX by this logic: If row n in column A have value equal or less than 2, then values of row n and (n-1) (current and previous rows of calculated columns) of columns B will be 1.

Plz help me. Thank you so much.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I assume your model has a kind of order column (or Date_column) for ordering [Column A], like the below example.

Please check the below picture and the attached pbix file whether it suits your requirement.

Column B CC =
VAR _currentColumnA = Data[ColumnA]
VAR _nextColumnA =
    MAXX (
        FILTER ( Data, Data[ColumnA_order] = EARLIER ( Data[ColumnA_order] ) + 1 ),
        Data[ColumnA]
    )
RETURN
    SWITCH (
        TRUE (),
        _nextColumnA <= 2
            && _nextColumnA > 0, 1,
        _currentColumnA <= 2, 1
    )

If you have other groups in the dataset, the group has to be considered in the DAX formula.

[Phuonghm22]

Thank you so much for your reply.

Yes, it does have a date column (sorry for not bring in entirely context).

But I wonder if there is any solution that we don't have to use VAR, because my dataset is kind of big, and if I use VAR and FILTER at the same time, it works so long that I could not see the result.

Thank you and wait for your reply.