DAX for MTD Weekdays - Same Month Prior Year

[Dave1mo1]

Hi,

I'm looking for dax to get a count of weekdays last year MTD to normalize some sales data based on this variable. My data is always one day old (so yesterday), and my date table doesn't update to "today" - it only updates to the date of my most recent order/invoice. This dax seems to work, but the data uses "Today" function, so will be inaccurate each day, I think, until the data is updated and pushes the date table to include yesterday. Any help would be greatly appreciated!

PY MTD Count of Work Days =

CALCULATE (

    COUNTROWS ( 'FY Date Table' ),

    FILTER (

        'FY Date Table',

        'FY Date Table'[Day of Month] <= DAY ( TODAY () )-1

            && 'FY Date Table'[Month] = MONTH ( TODAY () )

            && 'FY Date Table'[Weekday] <= 6

            && 'FY Date Table'[Weekday] >1

            && 'FY Date Table'[Year] = year (TODAY())-1

    )

)

MTD Count of Work Days =

CALCULATE (

    COUNTROWS ( 'FY Date Table' ),

    FILTER (

        'FY Date Table',

        'FY Date Table'[Day of Month] <= DAY ( TODAY () )-1

            && 'FY Date Table'[Month] = MONTH ( TODAY () )

            && 'FY Date Table'[Weekday] <= 6

            && 'FY Date Table'[Weekday] >1

            && 'FY Date Table'[Year] = year (TODAY() )

    )

)

[Dave1mo1]

Thank you for the response, and sorry for the delay! I usually get an emailed notification when someone replies, and I didn't receive it this time.

I'm struggling to create the variable in the new measure - it won't find this column in my new table. Do I need to create a relationship of some kind between tables?

[Anonymous]

Hi @Dave1mo1 ,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Dave1mo1 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[aduguid]

Create a table 'Properties' and add one calculated column 'Today Date'. Then set it to TODAY(). Change your DAX measures to reference this date. When your refresh runs it will update. 

PY MTD Count of Work Days =
VAR _today = 'Properties'[Today Date]
VAR _result = CALCULATE (
    COUNTROWS ( 'FY Date Table' ),
    FILTER (
        'FY Date Table',
        'FY Date Table'[Day of Month] <= DAY ( _today )-1
            && 'FY Date Table'[Month] = MONTH ( _today )
            && 'FY Date Table'[Weekday] <= 6
            && 'FY Date Table'[Weekday] >1
            && 'FY Date Table'[Year] = year (_today)-1
    )
)
RETURN 
_result