Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
  • 1.  Exclude the current week ?

    Silver Contributor
    Posted 12 days ago
    Exclude the current week ?

    Hi there ,

    I want to show the chart like below -

    Last 4 weeks chart -

    suvechha_0-1637620481656.png

    How to achieve this, which will exclude the current week 47 ?

     

    My Master Calendar - 

    Date Range Filter = IF(AND
                (MasterCalendar[Date] > TODAY() -30, MasterCalendar[Date]<TODAY())
                --------------------------------------------------------
                , 1 , 0)
    So its taking Week 47 ... How can I exlude the current week ?
     

    req.PNG

     

    My Lastest chart is showing like below -

    result.PNG



  • 2.  RE: Exclude the current week ?

    Bronze Contributor
    Posted 11 days ago
    Try shifting your date filter field by 7 days.


    Date Range Filter = IF(AND
                (MasterCalendar[Date] > TODAY() -30, MasterCalendar[Date]<TODAY())

    to

    Date Range Filter = IF(AND
                (MasterCalendar[Date] >( TODAY()-7) -30, MasterCalendar[Date] < (TODAY())-7)


    ------------------------------
    Wallo Atkinson
    Green Bay
    ------------------------------



  • 3.  RE: Exclude the current week ?

    Top Contributor
    Posted 11 days ago
    Hi Suvechha:

    I wonder if you can try this calculation below for your filter. You would need to have another new column for Year Week No.

    IF (

        [Admitted ETP],

        VAR LastWeekDay =  MAX ( 'Date'[Day of Week Number] )

        VAR LastYearWeekAvailable = MAX ( 'Date'[Year Week Number] )

        VAR Value =

            CALCULATE (

                [Admitted ETP],

                ALLEXCEPT ( 'Date', 'Date'[Day of Week Number] ),

                'Date'[Day of Week Number] = LastWeekDay,

                'Date'[Year Week Number] <= LastYearWeekAvailable

            )

        return

            IF(Value, 1, 0

    )

    I hope this helps!



    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------



  • 4.  RE: Exclude the current week ?
    Best Answer

    Posted 10 days ago

    Hi,
    I suggest to use 'rank' columns in your calendar. I have this way in all my projects and they working well.

    'Rank' calculated column:

    Rank Week = RANKX('DIM Calendar','DIM Calendar'[Wk Sort],,ASC,Dense)


    'Rank Related' calculated column:

    Rank Week Related = 'DIM Calendar'[Rank Week] - LOOKUPVALUE('DIM Calendar'[Rank Week],'DIM Calendar'[Wk Sort],YEAR(TODAY())*100+WEEKNUM(today()))


    How it works?
    Your current week have always rank '0'.
    In your report/visual you set filter on Rank Related column as 'is less than'  and value 0.

    It's very usefull if your customer ask for different time range on pages. eg. last 10 weeks, last 12 weeks, last 4 weeks ets. You just set filter as 'is greater than' on -10, -12 or -4
    Like this:


    I hope it helps.

    Regards,
    Adam

    PS. An advice from me - Take OFF time from your Date columns. Date is date. Time is time.



    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 5.  RE: Exclude the current week ?

    Bronze Contributor
    Posted 10 days ago
    I like this answer!   Very similar to what I normally do.
    I call my field Week Offset.  I also normally generate a Year Offset, Month Offset and Quarter Offset.  With these fields along with a YTD Flag, it's easy to create many custom measures and makes it very easy for end users to filter to pretty much anything they need.

    For my week offset field,  I don't normally do a rank though.  Normally,  I already have the weekending date in a column in my calendar.    So my week offset is just a calculation of current day weekending date minus row weekending date divided by 7.  Current week is 0 and prior weeks are 1,2,3 etc.  Future weeks are negative.  I just like how that looks better.

    ------------------------------
    Wallo Atkinson
    Green Bay
    ------------------------------



  • 6.  RE: Exclude the current week ?

    Posted 9 days ago

    Thank you!,
    Unfortunatelly I can't use Microsoft date logic as in my main Workspace as my customer use custom calendar (weeks, months) so all my calendar is handmade :)
    eg. 01/01/2021 belongs to WK 53 Year 2020 :) And 02/01/2021 it's week 01, not 02 as think MS. So calendar is fully customized.

    And I agree. Rank give me flexible I need.

    Cheers,



    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 7.  RE: Exclude the current week ?

    Top Contributor
    Posted 10 days ago
    Hello:

    Actually the easiest way to handle your issue is for you to add a [Week Ending] column in Power Query to your date table.

    Then add this custom column also in PQ to your Date Table.

    Week Completed = [WeekEnding] < Date.From(Date.EndOfWeek(Date.From (DateTime.LocalNow())))

    This will answer TRUE for all completed weeks. Other users will understand this method.

    Thanks and please mark as accepted solution if it is.

    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------