Switch between 'Year to Date' and 'Week to Date'

[Anonymous]

Hello,

There is a between Date slicer on the report with several other slicers and charts. Here the requirement is user should be able to toggle between ‘Year To Date’ and ‘Week to Date’. The default Week Range on YTD view is from 28.08.23 to current Date.  Here we are not considering current week and previous week. Instead we are considering Previous week and 2nd Previous week in report as the current week data may be delayed entries in DB.
When a user switch to the week to date view using toggle, there are two possibilities:

1. if the standard filters are unchanged before switching to the WTD view, we should see the numbers/percentages for the last complete week (a complete week is Monday to Sunday so if today is 31/07/24 then the last complete week will be 22nd-28th July). Below that you will show the previous week numbers/percentages and the difference i.e. 15th-21st July.

2. if the week range and/or the academic year has been changed before switching in YTD view or the user changes them when on the WTD view, the we should be showing the numbers/percentages for the relevant last complete week in that range e.g. year remains 23/24 but range is 28/08/23 to 22/05/24 then the WTD option should show the numbers/percentages for the last complete week prior to 22/05/24 (13th-19th May). Below that you will show the previous week numbers/percentages and the difference i.e. 6th-12th May. 

If it was only 1, Then I created a Week offset column and created measures using offset values -1,-2 etc and then applied -1 as a filter on the WTD view. This method does show up Last week data But in this case the date slicer shows up only that week with -1 offset and also user will not be able to see other values selecting different date ranges. So I am stuck as I have to achieve both conditions as stated above.

So I think to satisfy both conditions the only source to consider is Max date in date slicer, The week where this max date falls(For example 03.08.24)then  the data on WEEK to Date toggled view data on cards must show up Previous week (22.7.24 to 28.7.24) for some cards and 2nd Previous week data (15.07.24 to 28.7.24) for some cards. Similary if a user selects different Week range the data must change accordingly on WTD view.

Any help is appreciated!

[Anonymous]

Hi, @Anonymous 

Thank for vanessafvg positive reply. You can try to use following measure to achieve your need.

Measure:

Value 1 = 
VAR _maxDate =
    MAX ( 'Table'[Date] )
VAR _maxWeekNum =
    WEEKNUM ( _maxDate )
VAR _previousCompleteWeekStartDay =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        WEEKNUM ( 'Table'[Date] ) = ( _maxWeekNum - 1 ),
        YEAR ( 'Table'[Date] ) = YEAR ( _maxDate )
    )
VAR _previousCompleteWeekEndDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        WEEKNUM ( 'Table'[Date] ) = ( _maxWeekNum - 1 ),
        YEAR ( 'Table'[Date] ) = YEAR ( _maxDate )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Choice[Choice] ),
        "WTD",
            IF (
                _previousCompleteWeekStartDay <> BLANK (),
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    'Table'[Date] >= _previousCompleteWeekStartDay
                        && 'Table'[Date] <= _previousCompleteWeekEndDay
                ),
                "Date range error"
            )
    )


Value 2 = 
VAR _maxDate =
    MAX ( 'Table'[Date] )
VAR _maxWeekNum =
    WEEKNUM ( _maxDate )
VAR _previousCompleteWeekStartDay =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        WEEKNUM ( 'Table'[Date] ) = ( _maxWeekNum - 2 ),
        YEAR ( 'Table'[Date] ) = YEAR ( _maxDate )
    )
VAR _previousCompleteWeekEndDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        WEEKNUM ( 'Table'[Date] ) = ( _maxWeekNum - 2 ),
        YEAR ( 'Table'[Date] ) = YEAR ( _maxDate )
    )
VAR _daysCurrentMonth =
    DAY ( _maxDate )
VAR _previousYear =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Date]
                = EOMONTH ( _maxDate, -13 ) + _daysCurrentMonth
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Choice[Choice] ),
        "WTD",
            IF (
                _previousCompleteWeekStartDay <> BLANK (),
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    'Table'[Date] >= _previousCompleteWeekStartDay
                        && 'Table'[Date] <= _previousCompleteWeekEndDay
                ),
                "ERROR"
            ),
        "YTD",
            IF (
                _previousYear <> BLANK (),
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    'Table'[Date] >= _previousYear
                        && 'Table'[Date] <= TODAY ()
                ),
                "Date Range Error"
            )
    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Hello @Anonymous  Thank you very much for your response but actually in th above file, the WTD and YTD is in a table column and used as a slicer but requirement is in the report, the default page is YTD and a toggle is introduced to navigate to WTD. As mentioned in question, if the user picks up any date in YTD page, WTD page, in WTD page the data must show up 1 week ago (whole of 'week to date' page)  and 2 week ago(PREv week column  in small table) as posted in above image. Also if a user does not change the YTD date but simply navigates to YTD page then and the page must show up data for recent  1 week ago
and 2 week ago.

[Anonymous · Accepted solution]

Hi, @Anonymous 

You can use Botton and Bookmark together to switch pages and set the initial state of the Card. If you change the date in WTD, which in turn affects the value in YTD, you can use Sync Slicer. For weekly offset values, I think you can just use the function Weeknum([column],2) to set Monday through Sunday as the weekly setting.

Create buttons in Power BI reports - Power BI | Microsoft Learn
Create report bookmarks in Power BI to share insights and build stories - Power BI | Microsoft Learn
Enable the Sync Slicers feature in Power BI visuals - Power BI | Microsoft Learn
WEEKNUM function (DAX) - DAX | Microsoft Learn

pre 1 week value = 

VAR _maxDate =
    MAX ( 'Table'[Date] )
VAR _maxWeekNum =
    WEEKNUM ( _maxDate , 2)
VAR _previousCompleteWeekStartDay =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        WEEKNUM ( 'Table'[Date] ) = ( _maxWeekNum - 2 ),
        YEAR ( 'Table'[Date] ) = YEAR ( _maxDate )
    )
VAR _previousCompleteWeekEndDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        WEEKNUM ( 'Table'[Date] ) = ( _maxWeekNum - 2 ),
        YEAR ( 'Table'[Date] ) = YEAR ( _maxDate )
    )
RETURN
    IF (
        _previousCompleteWeekStartDay <> BLANK (),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            'Table'[Date] >= _previousCompleteWeekStartDay
                && 'Table'[Date] <= _previousCompleteWeekEndDay
        ),
        "ERROR"
    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[vanessafvg]

please can you provide a visual example of what your expectation is. 

[Anonymous]

Hi @vanessafvg  As of now I am not able to provide visual example as I have not acheived it but to elaborate the question we have Date slicers in both pages YTD and WTD and both conditions above must work. I have posted few images here from WTD page. In the below image the card represents the 1 week ago data, Previous Week column represents 2 week ago data/counts and difference. We are not considering Current Week data.