CODEWITHSUNDEEP
powerbidax
Brian Samson
Brian Samson

Reputation: 116

Rank non-contiguous dates with a monthly reset

With reference to the below sample data, I have a table in Power BI that currently only contains the Bus_Day column. I am attempting to add the MTD column (in red) that counts the monthly cumulative number of Bus_Day. The rows highlighted in yellow are reflective of the requirement that the MTD Day column should reset to 1 for the first Bus_Day per month.

My challenge is the Bus_Day column does not contain contiguous dates, so using DATEDIFF is not an option.

enter image description here

Upvotes: 0

Views: 211

Answers (1)

Marcus
Marcus

Reputation: 4005

For this type of problem where you want to label non-contiguous dates, RANKX is your go-to unless you can solve it further upstream in your stack.

Here is a DAX column solution that adds a calculated column according to your specifications:

MTD Day = 
VAR _yr = YEAR ( [Bus_Day] ) 
VAR _mth = MONTH ( 'Table'[Bus_Day] )
RETURN
    RANKX ( 
        FILTER (
            'Table' , 
            YEAR ( [Bus_Day] ) = _yr 
            && MONTH ( [Bus_Day] ) = _mth
        ), 
        [Bus_Day] , , ASC 
    )

You can also calculate this using a measure, this formula does the trick with your sample data:

MTD Day Measure = 
VAR _day = SELECTEDVALUE ( 'Table'[Bus_Day] )
VAR _tbl = 
    FILTER (
        ALL ( 'Table' ) , 
        YEAR ( 'Table'[Bus_Day] ) = YEAR ( _day ) 
        && MONTH ( 'Table'[Bus_Day] ) = MONTH ( _day )
    )
RETURN
    RANKX ( 
        _tbl, 
        CALCULATE ( SELECTEDVALUE ( 'Table'[Bus_Day] ) ) , , ASC 
    )

Result:

enter image description here

Upvotes: 1

Related Questions