locked
Remove weekends using DAX tabular model RRS feed

  • Question

  • Hi All,

    I have a requirement to exclude weekends from report using DAX in tabular model or any other way. below here following scenerio:

    Tables:

    1. Transaction table: Columns are trans_date, trans_id and trans_amount

    2. Calendar table: Columns are Date, month, quarter and year

    In transaction table: I had adjusted weekends directly in transaction table means there are no weekend transactions available in table.

    Below measure I created to view the closing balance:

    [Closing Balance]: Calculate(SUM([trans_amount]),Filter(ALL(Calendar),Calendar[Date] <= MAX(Calendar[Date])))

    If I am extracting data along with 'Calendar'[Date] using above measure in power pivot, then data visible in weekends.

    I want to exclude this weekends from report itself.

    Note: [Closing Balance] measure is a cummulative sum of transaction amount that's why data visible in weekends.   

    Wednesday, April 6, 2016 5:28 AM

Answers

  • Hi,

    in you calendar table, add a calculated column that indicates if the date in the current record is weekend or not.

    then in your measure, add a filter on this calculated column.

    could this work for you ?

    • Proposed as answer by driezl Wednesday, April 6, 2016 6:22 AM
    • Marked as answer by ManikantM Wednesday, April 6, 2016 9:24 AM
    Wednesday, April 6, 2016 6:21 AM

All replies

  • Hi,

    in you calendar table, add a calculated column that indicates if the date in the current record is weekend or not.

    then in your measure, add a filter on this calculated column.

    could this work for you ?

    • Proposed as answer by driezl Wednesday, April 6, 2016 6:22 AM
    • Marked as answer by ManikantM Wednesday, April 6, 2016 9:24 AM
    Wednesday, April 6, 2016 6:21 AM
  • Thanks driezl,, its working for me.... nice solution....
    Wednesday, April 6, 2016 9:24 AM