Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
canddig
Frequent Visitor

My Date Calculation is showing blank all of a sudden

Hello, 

 

Can anyone help me trouble shoot why my dax formula would suddenly calculate (blank)? It has never done this to me before. 

 

WorkingDay = VAR UsedDate = Today() -2
VAR FirstDayOfMonth =
DATE( YEAR( UsedDate ), MONTH( UsedDate ), 1 )
VAR WorkDays =
COUNTROWS(
FILTER(
ADDCOLUMNS(
CALENDAR(
FirstDayOfMonth,
UsedDate
),
"WeekDayNumber", WEEKDAY(
[Date],
2
)
),
[WeekDayNumber] < 6
)
)

RETURN
WorkDays
2 REPLIES 2
daxer-almighty
Solution Sage
Solution Sage

Very simple if you run this code in DAX Studio:

 

 

EVALUATE
VAR UsedDate = TODAY() - 2 // today must be 2021-05-03
VAR FirstDayOfMonth =
    DATE( YEAR( UsedDate ), MONTH( UsedDate ), 1 )
VAR WorkDays =
    ADDCOLUMNS(
        CALENDAR(
            FirstDayOfMonth,
            UsedDate
        ),
        "WeekDayNumber",
            WEEKDAY(
                [Date],
                2
            )
    )
    -- [WeekDayNumber] < 6
RETURN
    WorkDays

 

Thanks! I am still getting an error and I am not sure why? I am seeing it as infinity in calculations or (blank)

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.