Hide saturdays and sundays without excluding them from measure calculations

[Anonymous]

Hi, I've created a visual matrix in which I've the date as rows and some values for each columns. What I would like to do is to hide sundays and saturdays (in which most values are blank) from the visual, but If I select all the day names except them the values change because it is used in the column measures. Is there a way to only hide these days but keeping them in the calculations? thanks. Here's a screen of my report.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Here I have created a sample for your reference, please check the following steps as below.

1. Create a calcualted column in date table.

Column = IF(WEEKDAY('Table'[Date],2)>5,0,1)

2. Then we can filter the visual by the calculated column like the picture as below.

For more detials, please check the pbix as attached.

[Jimmy801]

Hello @Anonymous 

only know I saw that you asked to keep the rows in, but without calculation

Then my solution won't work. You have to go for DAX and create a new measure like this

NewSum = SUMX('Table';if(WEEKDAY('Table'[Date];2)<5;SUM('Table'[Value]);0))

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 , I appreciateyour answer, but I want to hide the row but maintain the calculations and not viceversa. If I exclude sunday and saturday with a filter, the values in the other days change and I don't want that.

[Jimmy801]

Hello @Anonymous ,

any news here?

Jimmy

[Mariusz]

Hi @Anonymous 

Try something like below.

Measure = 
VAR __values = SUM( 'Table'[Value] )
RETURN 
IF( 
    ISINSCOPE( 'Calendar'[Date] ), 
    IF(
        NOT( WEEKDAY( SELECTEDVALUE( 'Calendar'[Date] ), 2 ) IN { 6, 7 } ), 
        __values ), 
    __values 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Jimmy801]

Hello @Anonymous 

then go for this solution. This should exclude the calculation of weekends, but in the subtotals should be considered

newsum1 = SWITCH(SELECTEDVALUE('Table'[Date]); BLANK(); SUM( [Value]);SUMX(FILTER('Table'; WEEKDAY('Table'[Date];2)<5);[Value]))

This datatable 

should result in this

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Jimmy801]

Hello @Anonymous 

is this data passed somehow in Power Query?

Supposing the date is a column, you can filter the date-field for working days. 

See an example, how this can be achieved

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzTSMzIwtFTSUXJyDQKSBUX56UDKUClWByhvhlVeNze1OBXINoIoQjbEJ9QdYYgxpiEIeZghJpiGDJxLYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Spalte1 = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text}, {"Spalte1", type text}, {"Value", Int64.Type}}),
    SelectWorkingDays = Table.SelectRows
    (
        #"Changed Type", 
        each Date.DayOfWeek(_[Date],Day.Monday)<5
    )
in
    SelectWorkingDays

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy