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

[bhaskarpbi999]

Hi,

 I have some requirement which can be implemented by DAX.

There is a Date working table which has data of year and number of operating days for current year and previous year.

Existinfg table dat below

There is a DWH fact table from where revenue is caluclated for current year and previous years but caculation is based on current MTD as we are loading DHW data on Weekly basis every thurday.

When user selects Feb month filter of year 2024 the DAX caluclations should be like below. with two measures.

First Measure - DWH Revenue Feb 2024/21 (operating days for Feb 24 as shown in above screenshot)

Second Measure - DWH Revenue Feb 2025 Month to current date data /the count of operating days up to current date data excluding saturday and sundays *(Mutipled by) total Count of operating days for Feb'25 as shown in above screenshot.

Could you please help me to wirte DAX and always available to provide details of existing data.

Regards,

Bhaskar

[Power2G · Accepted solution]

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())

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[bhaskarpbi999]

Hi Fen,

Many thanks for your support and also for the solution.

Regards,

Bhaskar

[bhaskarpbi999]

Hi Fen Ling,

Thanks for the DAX formulas and Kudos for your great support.

We need the DAX formula logic which should work for current month dynamically when we select month from filter.
so let's say now feb'25, we want to calculate the first week revenue of Feb 25,the way we will calculate that is

First Measure - Feb24 revenue/operating days of feb 24 (it shoudl calculate for previous year same month based on filter selection)

Second Measure - for 2025 first week would be Week1 feb 25 revenue/5 days (Operaitng days excluding saturday and sunday)

We are using month filter from MonthCal table and condition should be based on YearMth Key.

DWH_ADJ fact table with Monthcal table relationship screenshot

I aploligixe for not specifying the exact requirement during post creation.

Many thanks for your supprt.

Regards,

Bhaskar

[Power2G · Accepted solution]

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())

[bhaskarpbi999]

Hi,

Many thanks for your support and also for the solution.

Regards,

Bhaskar

[bhaskarpbi999]

Hi Power Sir,

Thanks for the DAX formulas and Kudos for your great support.

We need the DAX formula logic which should work for current month dynamically when we select month from filter.
so let's say now feb'25, we want to calculate the first week revenue of Feb 25,the way we will calculate that is

First Measure - Feb24 revenue/operating days of feb 24 (it shoudl calculate for previous year same month based on filter selection)

Second Measure - for 2025 first week would be Week1 feb 25 revenue/5 days (Operaitng days excluding saturday and sunday)

We are using month filter from MonthCal table and condition should be based on YearMth Key.

I aploligixe for not specifying the exact requirement during post creation.

Many thanks for your supprt.

Regards,

Bhaskar

[bhaskarpbi999 · Accepted solution]

Hi ,

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