Forum Discussion

butlerjay87's avatar
butlerjay87
Icon for Helper I rankHelper I
6 years ago
Solved

Removing incomplete weeks from daily dataset

Scenario:
Daily data being received and visual can be refreshed daily if required. I do have a DimDate file setup but I am also happy to use the source table date info if required/easier.
I have grouped the activity to weekly as we feel it looks cleaner (especially when weekend activity is significantly reduced).
When I refresh in the middle of a week, this is what the outcome is:
As you can see, the Total OP Activity for Week Starting 19/10/2020 is only 8,351 because there have only been 3 days but the previous is still a "full" week as it is getting the data for the same week of last year.
 
I don't want to leave it as it is because it looks like we are only doing 40% of the activity.
 
Thank you in advance!
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    You can build something into your measure to return blank in the current week like this.

     

    NewMeasure =
    VAR thisdateeow =
        MAX ( 'Date'[Date] )
            - WEEKDAY (
                MAX ( 'Date'[Date] )
            ) + 8
    VAR thiseow =
        TODAY ()
            - WEEKDAY (
                TODAY ()
            ) + 8
    RETURN
        IF (
            thisdateeow < thiseow,
            [Your Measure],
            BLANK ()
        )

     

     

    Regards,

    Pat

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    You can build something into your measure to return blank in the current week like this.

     

    NewMeasure =
    VAR thisdateeow =
        MAX ( 'Date'[Date] )
            - WEEKDAY (
                MAX ( 'Date'[Date] )
            ) + 8
    VAR thiseow =
        TODAY ()
            - WEEKDAY (
                TODAY ()
            ) + 8
    RETURN
        IF (
            thisdateeow < thiseow,
            [Your Measure],
            BLANK ()
        )

     

     

    Regards,

    Pat

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    It's very easy: when you reach out for data of the same week last year, do not grab all the days of the week but as many days as there are days in the week to which you want to compare. For such calculations you have to have a good calendar.