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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ringovski
Helper II
Helper II

Calendar include blanks

Hi,

I have a start & end date with some blanks in the rows which I would like included, I need to handle them differently in the visual layer. However the DAX calendar function says blanks are not allowed.

 

I tried adding a IF(ISBLANK()) but this returns a error saying end date must be larger than start date, so I'm out of ideas now.

Delivery Days = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( 
                'Toll_Shipments'[Pick Up Date],
                Toll_Shipments[Actual Delivered Date]
                  ),
            "Is Weekday",WEEKDAY([date],2)<6
            ,"Is Holiday", CONTAINS('Holiday Dates','Holiday Dates'[Dates],[Date] )
        ),
        [Is Holiday] = FALSE() 
           && [Is Weekday] = TRUE()
    )
)

 

ringovski_0-1647221293067.png

ringovski_1-1647221371756.png

 Thanks for any ideas or assistance.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@ringovski , Try like

 

Work Day = if( not(isblank([Pick Up Date])) && not(isblank([Actual Delivered Date])) , COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Pick Up Date],Table[Actual Delivered Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)), blank())

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@ringovski , Try like

 

Work Day = if( not(isblank([Pick Up Date])) && not(isblank([Actual Delivered Date])) , COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Pick Up Date],Table[Actual Delivered Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)), blank())

Thanks for reply should have thought that myself.

Anonymous
Not applicable

Does the code fixed your problem? I come across to have error using the code above. 

 

Appreciate your help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors