Forum Discussion
Sum weekend values on monday
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
Super UserHi 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.
