Forum Discussion

Analitika's avatar
Analitika
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Exclude weekend from date column in Power BI

Hello,

 

I need to ask how I could exclude weekends from date column, leaving date only weekday.

Here is dax of date column:

 

Calendar = ADDCOLUMNS (
CALENDAR (
DATE ( min ('_Year BRIDGE'[Year] ), 1, 1),
DATE ( max ('_Year BRIDGE'[Year] ), 12, 31)
),
    "Year", YEAR([Date]),
    "MonthNo", MONTH([Date]),
    "Month", FORMAT([Date],"mmm"),
    "Quarter", FORMAT([Date],"\Qq"),
"YearQuarter", FORMAT([Date],"YYYY \QQ"),
    "WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat
    "Weekday", FORMAT([Date],"ddd") )

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Analitika 

     

    Try to add a visual level filter by using a measure:

    Measure = IF(MAX('Calendar'[WeekdayNo])<>6&&MAX('Calendar'[WeekdayNo])<>7,1)

    Set show items when the value is 1.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

12 Replies

  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    4 years ago
    Create calculated column and filter on blank
    Flag to Exclude = IF( 'Calendar'[WeekdayNo] = 7 || 'Calendar'[WeekdayNo] = 6, "X")
  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    HI Analitika ,

     

    Try this:-

    Calendar =
VAR Dates =
    ADDCOLUMNS (
        CALENDAR (
            DATE ( MIN ( '_Year BRIDGE'[Year] ), 1, 1 ),
            DATE ( MAX ( '_Year BRIDGE'[Year] ), 12, 31 )
        ),
        "Year", YEAR ( [Date] ),
        "MonthNo", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "mmm" ),
        "Quarter", FORMAT ( [Date], "\Qq" ),
        "YearQuarter", FORMAT ( [Date], "YYYY \QQ" ),
        "WeekdayNo", WEEKDAY ( [Date], 2 ),
        //1-Sun..Sat, 2-Mon..Sat
        "Weekday", FORMAT ( [Date], "ddd" )
    )
RETURN
    FILTER ( Dates, NOT ( [WeekdayNo] IN { 6, 7 } ) )

     

    Thanks,

    Samarth

    • Analitika's avatar
      Analitika
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      I need create a new date column which will be  filtered according to weekday.

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Analitika , That code doing the same. Its filtered out the weekends and weekdays remains.

    • Analitika's avatar
      Analitika
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Yes I have created a new table but problem is that I still see gaps in visual.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Analitika 

     

    Try to add a visual level filter by using a measure:

    Measure = IF(MAX('Calendar'[WeekdayNo])<>6&&MAX('Calendar'[WeekdayNo])<>7,1)

    Set show items when the value is 1.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.