How to calculate Prior Year Running Total based on Workdays (MTD & YTD) in Power BI?

[AnilKapkoti]

Question:

I am trying to calculate cumulative Net Revenue based on workdays instead of calendar days.

In my model, weekends are assigned the same workday number as the previous weekday.
For example:

• Week 1 → 1, 2, 3, 4, 5, 5, 5

• Week 2 → 6, 7, 8, 9, 10, 10, 10

  This logic works fine for Current Year (CY) MTD and YTD Running Totals, where I use the following measures:

   

  CY MTD Running total = VAR mxWorkday = MAX('Date'[Workday Number]) VAR mxYear = MAX('Date'[Year]) VAR mxMonth = MAX('Date'[Month No]) RETURN CALCULATE( [Net Revenue], FILTER( ALLSELECTED('Date'), 'Date'[Year] = mxYear && 'Date'[Month No] = mxMonth && 'Date'[Workday Number] <= mxWorkday ) ) CY YTD Running total = VAR mxWorkday = MAX('Date'[Workday Number YTD]) VAR mxYear = MAX('Date'[Year]) RETURN CALCULATE( [Net Revenue], FILTER( ALLSELECTED('Date'), 'Date'[Year] = mxYear && 'Date'[Workday Number YTD] <= mxWorkday ) )

  The challenge is when I try to calculate the Prior Year (PY) Running Totals on the same workday logic.

  Issues I’m facing:

  1. Workday numbers don’t match between CY and PY (e.g., different number of workdays in the same month).

  2. If PY has fewer/more workdays than CY, the logic breaks.

  3. When a month starts on Saturday/Sunday, some days have revenue but zero workdays, which causes inconsistencies.

  4. I need the solution to be dynamic for both MTD and YTD.

  5. Handling leap years with workday logic is also a concern.

     Here’s the test logic I tried for PY, but it’s not working as expected:

      

     Test3 = VAR mxYear = MAX('Date'[Year]) - 1 VAR mxMonth = MAX('Date'[Month No]) VAR PYdate = EOMONTH(EDATE(MAX('Date'[Date]), -12), 0) VAR mxWorkday = CALCULATE( MAX('Date'[Workday Number YTD]), 'Date'[Year] = mxYear && 'Date'[Month No] = mxMonth ) VAR mxWorkdayCY = CALCULATE( MAX('Date'[Workday Number YTD]), 'Date'[Year] = mxYear + 1 && 'Date'[Month No] = mxMonth ) VAR _Condition = IF(mxWorkdayCY > mxWorkday, mxWorkdayCY, mxWorkday) RETURN CALCULATE( [test], FILTER( ALLSELECTED('Date'), 'Date'[Year] = mxYear && 'Date'[Month No] = mxMonth && 'Date'[Workday Number] <= _Condition ) )

     Has anyone dealt with this type of workday-based cumulative calculation for Prior Year?
     How can I align CY and PY running totals dynamically, considering differences in workdays, weekends, and leap years?
     
     @rohit1991

     @danextian @amitchandak @Greg_Deckler 

[v-echaithra]

Hi @AnilKapkoti ,

May I ask if you have resolved this issue? Please let us know if you have any further issues with sample data, we are happy to help.

Thank you.

[v-echaithra]

Hi @AnilKapkoti ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required, please provide sample data. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[FarhanJeelani]

Hi @AnilKapkoti,
Determine the Maximum Workday Number for the Current Year (CY) and Prior Year (PY)

For CY MTD: Use the maximum Workday Number for the current month
For CY YTD: Use the maximum Workday Number YTD
For PY MTD: Use the maximum Workday Number for the same month in the prior year
For PY YTD: Use the maximum Workday Number YTD for the prior year
Calculate the PY Running Total

For PY MTD: Filter the data to the prior year and the current month, and sum the Net Revenue up to the maximum Workday Number for the prior year.
For PY YTD: Filter the data to the prior year, and sum the Net Revenue up to the maximum Workday Number YTD for the prior year.

Measure:

CY MTD Running Total

CY MTD Running Total = 
VAR MaxWorkday = MAX('Date'[Workday Number])
VAR MaxYear = MAX('Date'[Year])
VAR MaxMonth = MAX('Date'[Month No])
RETURN
    CALCULATE(
        [Net Revenue],
        FILTER(
            ALLSELECTED('Date'),
            'Date'[Year] = MaxYear
            && 'Date'[Month No] = MaxMonth
            && 'Date'[Workday Number] <= MaxWorkday
        )
    )

CY YTD Running Total

CY YTD Running Total =
VAR MaxWorkdayYTD = MAX('Date'[Workday Number YTD])
VAR MaxYear = MAX('Date'[Year])
RETURN
    CALCULATE(
        [Net Revenue],
        FILTER(
            ALLSELECTED('Date'),
            'Date'[Year] = MaxYear
            && 'Date'[Workday Number YTD] <= MaxWorkdayYTD
        )
    )

PY MTD Running Total

PY MTD Running Total =
VAR MaxYear = MAX('Date'[Year]) - 1
VAR MaxMonth = MAX('Date'[Month No])
VAR MaxWorkdayPY = CALCULATE(
    MAX('Date'[Workday Number]),
    'Date'[Year] = MaxYear
    && 'Date'[Month No] = MaxMonth
)
RETURN
    CALCULATE(
        [Net Revenue],
        FILTER(
            ALLSELECTED('Date'),
            'Date'[Year] = MaxYear
            && 'Date'[Month No] = MaxMonth
            && 'Date'[Workday Number] <= MaxWorkdayPY
        )
    )

PY YTD Running Total

PY YTD Running Total =
VAR MaxYear = MAX('Date'[Year]) - 1
VAR MaxWorkdayYTDPY = CALCULATE(
    MAX('Date'[Workday Number YTD]),
    'Date'[Year] = MaxYear
)
RETURN
    CALCULATE(
        [Net Revenue],
        FILTER(
            ALLSELECTED('Date'),
            'Date'[Year] = MaxYear
            && 'Date'[Workday Number YTD] <= MaxWorkdayYTDPY
        )
    )

Please mark this post as solution if it helps you. Appreciate Kudos.

[AnilKapkoti]

Hi @FarhanJeelani ,

Thank you for your response. I tried using the solution, but unfortunately it’s not working as expected.
I made a small modification in the CY MTD Running Total to restrict the values up to the available day, but as you can see, the values are repeating for the future dates.

Apart from that the PY values are apperaing blank.

[v-echaithra · Accepted solution]

Hi   @AnilKapkoti ,

Try this measure: 
CY MTD Running Total =
VAR mxDate = MAX('Date'[Date])
VAR mxWorkday = MAX('Date'[Workday Number])
VAR mxYear = MAX('Date'[Year])
VAR mxMonth = MAX('Date'[Month No])
RETURN
CALCULATE( [Net Revenue],
FILTER(ALL('Date'),
'Date'[Year] = mxYear &&
'Date'[Month No] = mxMonth &&
'Date'[Workday Number] <= mxWorkday &&
'Date'[Date] <= mxDate
))

Try this test measure to see if PY dates are being picked up:

 

Test PY Dates =
CALCULATE(
    MAX('Date'[Date]),
    FILTER(
        ALL('Date'),
        'Date'[Year] = MAX('Date'[Year]) - 1 &&
        'Date'[Workday Number] = MAX('Date'[Workday Number])
    )
)

If this returns blank, your workday alignment needs refinement.

If possible, could you please provide more details about your data?

How to provide sample data in the Power BI Forum

You can refer the following link to upload the file to the community.
How to upload PBI in Community

Thank you.