Forum Discussion

escalas's avatar
escalas
Frequent Visitor
9 years ago
Solved

Sum weekend values on monday

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 advanc...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    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

     

MFelix's avatar
MFelix
Super User
9 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      MFelix
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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!