Forum Discussion

hume's avatar
hume
Regular Visitor
9 years ago

Filter on dates in a Graph

I have a date dimension from which I need to exclude weekends and holidays. In my Calendar table I have created a column that indicates if it is a workday or not. I use this in a Report level filter ...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

hume

 

I reproduced same issue as yours. It seems the Report Level filter can't filter out the member on chart Axis. I have reported this issue internally. Currently to work around this issue. I suggest you filter those rows in your dataset within Query Editor. Make the weekday less than 6.

 

 

Regards,

 

 

hume's avatar
hume
Regular Visitor
9 years ago

Thanks for the reply. Filtering out the weekends in the Query filter is not really an option, as those days also have data. The data is less than on workdays, which is why they should not appear in the day comparisons. But they can not completely be discarded, otherwise monthly reports would be wrong.

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    hume

     

    This is by design.

     

    Using the Double Arrow Down Drill, which will show a view of "days of the month" aggregated across all 365 days. This is what you did. Over a whole year, every day of the month is NOT a weekend at least once, so it gets aggregated. There will be no values missing, 1-31 are all valid days of the month with at least one non-weekend value being added up.

     

    You can arrive at "days" in multiple ways.

    First, change the "date hierarchy" to just "date" using the Field dropdown menu. This shows all 365 days, with the column's format string applied. This works as expected.

    Second, use Hierarchy Drill button (pitch fork icon) to drill to Day level, which will by default show concatenated labels. This also works.

     

    Best Regards,
    Herbert