DAX Calculation based on MTD and Operating days for current and previous year dynamically for months

Hi bhaskarpbi999 

Measure 1: DWH Revenue per Operating Day (Feb 2024)

DWH_Revenue_Per_Op_Day_Feb_24 = 
VAR Revenue_Feb_24 = CALCULATE( SUM(DWH_Fact[Revenue]), 'Date_Working'[Year] = 2024, 'Date_Working'[Month] = "February")
VAR Operating_Days_Feb_24 = LOOKUPVALUE('Date_Working'[DE Op Days], 'Date_Working'[Year], 2024, 'Date_Working'[Month], "February")
RETURN 
IF(Operating_Days_Feb_24 <> 0, Revenue_Feb_24 / Operating_Days_Feb_24, BLANK())

 Measure 2: DWH Revenue per Adjusted Operating Day (Feb 2025 MTD)

DWH_Revenue_Per_Adj_Op_Day_Feb_25 = 
VAR CurrentDate = MAX(DWH_Fact[Date])
VAR Revenue_Feb_25_MTD = CALCULATE( 
    SUM(DWH_Fact[Revenue]), 
    'Date_Working'[Year] = 2025, 
    'Date_Working'[Month] = "February",
    DWH_Fact[Date] <= CurrentDate
)
VAR Op_Days_Until_Today = 
    CALCULATE(
        COUNTROWS('Date_Working'), 
        'Date_Working'[Year] = 2025, 
        'Date_Working'[Month] = "February",
        'Date_Working'[Date] <= CurrentDate,
        NOT(WEEKDAY('Date_Working'[Date],2) IN {6,7})
    )
VAR Total_Op_Days_Feb_25 = LOOKUPVALUE('Date_Working'[DE Op Days], 'Date_Working'[Year], 2025, 'Date_Working'[Month], "February")
VAR AdjustedRevenue = (Revenue_Feb_25_MTD / Op_Days_Until_Today) * Total_Op_Days_Feb_25

RETURN 
IF(Op_Days_Until_Today <> 0, AdjustedRevenue, BLANK())

Hi, bhaskarpbi999 

Perhaps you can refer to the following DAX to create Measure:

First Measure:

DWH_Revenue_Feb_2024_Per_Op_Day = 
VAR Revenue_Feb_2024 = CALCULATE(
    SUM('DWH Fact Table'[Revenue]),
    'Date Working Table'[Year] = 2024,
    'Date Working Table'[Month] = "February"
)
VAR Operating_Days_Feb_2024 = CALCULATE(
    SUM('Date Working Table'[This Year DE Op Days]),
    'Date Working Table'[Year] = 2024,
    'Date Working Table'[Month] = "February"
)
RETURN
DIVIDE(Revenue_Feb_2024, Operating_Days_Feb_2024)

 Second Measure:

DWH_Revenue_Feb_2025_MTD_Adjusted = 
VAR CurrentDate = TODAY()
VAR Revenue_Feb_2025_MTD = CALCULATE(
    SUM('DWH Fact Table'[Revenue]),
    'Date Working Table'[Year] = 2025,
    'Date Working Table'[Month] = "February",
    'Date Working Table'[Date] <= CurrentDate
)
VAR Operating_Days_Feb_2025_MTD = CALCULATE(
    COUNTROWS('Date Working Table'),
    'Date Working Table'[Year] = 2025,
    'Date Working Table'[Month] = "February",
    'Date Working Table'[Date] <= CurrentDate,
    'Date Working Table'[IsWorkingDay] = TRUE()
)
VAR Total_Operating_Days_Feb_2025 = CALCULATE(
    SUM('Date Working Table'[This Year DE Op Days]),
    'Date Working Table'[Year] = 2025,
    'Date Working Table'[Month] = "February"
)
RETURN
DIVIDE(Revenue_Feb_2025_MTD, Operating_Days_Feb_2025_MTD) * Total_Operating_Days_Feb_2025

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi ,

unfortunately we dont have date in DWH_ADJ fact table and think it will be a problem