Sum weekend values on monday

[escalas]

Hi,

I need to sum the 'SR totales' values on weekends to the value on mondays. Saturday and Sunday are 03/06/2017 and 04/06/2017. 

Do you have any idea of how it can be done ? 

Thanks in advance.

[Anonymous]

Hey @MFelix , 

Let me try one more time 😀

Date range (selected in a date slicer) 2020-10-26 -- 2020-11-02 is a perfect example and has the following data.  

As you can see PDFs have been uploaded every day in between Oct 26 - Nov 2

When a new measure is added to reflect PDF count excluding weekends, I get the following results

As you can see Oct 26th count shows 178 (highlighted in yellow above) meaning the measure adds 131 (Oct 26th) + 1 (Oct 25th) + 46 (Oct 24th), while it should be 131 - excluding "outside the range" dates (Oct 24 & 25) from the calculation, as well as total for column "# of PDFs uploaded (excl. weekend) (2)" should be 816 (131+99+186+205+98+97) instead of 766.

On the other hand, calculation for Nov 2 (Mon) works exactly as it should -- 97 = 58 (Nov 2) + 5(Nov 1) + 34(Oct 31)

Problem # 2

When we add a new table to reflect total count of PDFs uploaded per Week day I get the following:

It shows cumulative total PDFs uploaded on Monday as 178 (highlighted in yellow above), again including "non-legit" data (Oct 24 and 25) that is outside of our range (Oct 26 - Nov 2).

In this row context, I'd like Monday calculation to be the following: 131 (Oct 26) + 34 (Oct 31) + 5 (Nov 1) + 58 (Nov 2) = 228, with the total for this column being 228 (Mon) + 99 (Tue) + 186 (Wed) + 205 (Thu) +98 (Fri) = 816

Ideally, I'd like results for these dates selected to be the following :

Below is another example with dates selected - 2018-8-20 -- 2020-11-27 

Problematic dates highlighted in yellow above.

I'd like results to be the following

  

Now that I'm writing this, it got me thinking, i should probably be using multiple measures for the two tables to accomplish my goal. In any case, thank you!

[MFelix]

Hi @Anonymous ,

Since you have a date table to make the relationaship you need to redo your PDF excluding weekends to the formula below:

# of PDFs uploaded (exlc. weekend) = --Counting weekend uploads as if they were made on the following Monday
VAR Current_day =
   MIN ('Date'[Date] )
VAR Saturday =
    MIN ( 'Date'[Date] ) - 2
RETURN
    IF (
       WEEKDAY( Current_day ) = 1
            || WEEKDAY( Current_day ) = 7 ;
        BLANK ();
        IF (
            WEEKDAY(Current_day) = 2 && MAX( 'Date'[Date] ) <= Current_day;
            CALCULATE (
                COUNT( BATCH_RECORD[ID] );
                FILTER (
                    ALLSELECTED ( 'Date' );
                     'Date'[Date] >= Saturday
                        && 'Date'[Date] <= Current_day
                )
            );
            COUNT( BATCH_RECORD[ID] )
        )
    )

Major change on this is the use of the ALLSELECTED on the filter part forcing the values to be within slicer context, also has a good practice since you are on a one to many relationship you should use the Date table has your filter of dates and not the BATCH_RECORD table.

As you can see below the calculation for excluding the weekend values are correct without any SUMX.

Next since you are removing the Date from your context you tabel you need to change the measure in that way you need to add an additional measure to calculate only when you have the weekday selected:

# of PDFs uploaded (excl. weekend) (TOTAL BY WEEKDAY) = 
IF (
    ISINSCOPE ( 'Date'[Day of Week] );
    SUMX (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Date'[Date]; 'Date'[Day of Week] );
                'Date'[Date];
                'Date'[Day of Week];
                "@UPLOADED_PDFS"; [# of PDFs uploaded (exlc. weekend)]
            );
            'Date'[Day of Week] = MAX ( 'Date'[Day of Week] )
        );
        [@UPLOADED_PDFS]
    );
    [# of PDFs uploaded (exlc. weekend)]
)

Has you can see below and in attach file believe calculations are correct:

Context is very important in measures especially when you are talking about this type of calculations based on conditional values because when you go to different levels, has is the case not all values will be picked up.

[Anonymous]

Thank you @MFelix , 

This is exactly what I was trying to acheve

Much appreciated!

[MFelix · Accepted solution]

Hi @escalas,

Created a small table with date and value, created a calculated measure with the following formula:

Value Weekend = 
VAR Current_day =
   MIN (Table1[Date])
VAR Saturday =
    MIN ( Table1[Date] ) - 2
RETURN
    IF (
       WEEKDAY( Current_day ) = 1
            || WEEKDAY( Current_day ) = 7,
        BLANK (),
        IF (
            WEEKDAY(Current_day) = 2,
            CALCULATE (
                SUM ( Table1[Values] ),
                FILTER (
                    ALL ( Table1 ),
                    Table1[Date] >= saturday
                        && Table1[Date] <= Current_day
                )
            ),
            SUM ( Table1[Values] )
        )
    )

The result is below please check if it is ok.

The first image is the detailed values and the second one is just using the measure if you need to "hide2 the weekend.

Regards,

MFelix

[Anonymous]

Hi @MFelix 

I have a similar requirment and came across this solution, this's great, thank you!  

I added an additiona validation step when Current_day is Monday to also check if the last reporting date is within the timeframe for when data is available - mainly to eliminate scenario when it is Saturday/Sunday and yet there is data displayed for an upcoming Monday (future)

# of PDFs uploaded (exlc. weekend) = --Counting weekend uploads as if they were made on the following Monday
VAR Current_day =
   MIN ( BATCH_RECORD[CREATED_AT_DATE] )
VAR Saturday =
    MIN ( BATCH_RECORD[CREATED_AT_DATE] ) - 2
RETURN
    IF (
       WEEKDAY( Current_day ) = 1
            || WEEKDAY( Current_day ) = 7,
        BLANK (),
        IF (
            WEEKDAY(Current_day) = 2 && Current_day <= MAX( BATCH_RECORD[CREATED_AT_DATE] ),
            CALCULATE (
                COUNT( BATCH_RECORD[ID] ),
                FILTER (
                    ALL ( 'Date' ),
                     'Date'[Date] >= Saturday
                        && 'Date'[Date] <= Current_day
                )
            ),
            COUNT( BATCH_RECORD[ID] )
        )
    )

What I'm struggling with is to calculate totals for all Mondays (incl. weekend), as it always returns the first Monday's data. In adition, total for this measure (the very last row) is not returning the correct result when Saturday/Sunday is the last reporting day, see below. Any thoughts how to address this? Thank you!!

[MFelix]

Hi @Anonymous 

Try to add this additional measure:

Total PDF's = SUMX(VALUES(Table[Day of the Week]), [# of PDFs uplodaded (excl. weekend)])

You can also change the Day of the week by another column depending on the context for example date.

If does not work can you share a sample of your data. 

[Anonymous]

Thanks @MFelix 

The new measure partially resolved the issue.

I'm sure this has something to do with the date slicer, especcially when the first date selected is Monday.

Attaching sample PBIX file here (Google Drive). Thank you for looking into it! 

[MFelix]

Hi @Anonymous,

On the tests I have made with your file everything is working properly, don't really understand what is the error you are getting:

I have selected start date has monday and works correctly. On the column with the dates you did not add the second measure that you created.

If this is not correct can you please specify what is the error or share a print screen so I can duplicate the error.