Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    According to your statement, I think you want to calculate the number of previous week and the number of 2nd previous week based on the last date in your slicer.

    Here I suggest you to try code as below. My Sample is easy a Data table with two columns [Date] and [Number] and a Calendar Table with date from 2023/08/28 to today.

    Previous Month Number = 
VAR _MAXDATE = MAX('Calendar'[Date])
VAR _PREVIOUSWEEKEND = _MAXDATE - WEEKDAY(_MAXDATE,2)
VAR _PREVIOUSWEEKSTART = _PREVIOUSWEEKEND - 6
RETURN
CALCULATE(SUM('Table'[Number]),FILTER(ALL('Calendar'),'Calendar'[Date]>=_PREVIOUSWEEKSTART && 'Calendar'[Date]<=_PREVIOUSWEEKEND))
    2nd Previous Month Number = 
VAR _MAXDATE = MAX('Calendar'[Date])
VAR _PREVIOUSWEEKEND = _MAXDATE - WEEKDAY(_MAXDATE,2)
VAR _2NDPREVIOUSWEEKEND = _PREVIOUSWEEKEND - 7
VAR _2NDPREVIOUSSTART = _2NDPREVIOUSWEEKEND - 6
RETURN
CALCULATE(SUM('Table'[Number]),FILTER(ALL('Calendar'),'Calendar'[Date]>=_2NDPREVIOUSSTART && 'Calendar'[Date]<=_2NDPREVIOUSWEEKEND))
    Difference = [Previous Month Number] - [2nd Previous Month Number]
    Difference% = DIVIDE([Difference],[2nd Previous Month Number])

    Result is as below.

    If this reply still couldn't help you solve your issue, please share a sample file with us.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Icon for Super User rankSuper User
    2 years ago

      

    Since you have already created a week offset column, Your WTD measures should identify the latest week weekoffset number and you pass the week offset value as filter for calculating latest week value and similarly you can calculate the last to last week value.

     

     

    Need a Power BI Consultation? Hire me on Upwork

     

    Connect on LinkedIn

     




    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.

    Proud to be a Super User!

    Anonymous

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you  tharunkumarRTK Yes I tried with measures as well For example - 

      Current Week_Sales = CALCULATE([Total Sales],DateTable[WeekOffset]=-1) but this is not working in line with Date Range Slicer. It Just considers the Previous Week(Monday to Sunday) but the expectation is if user selects the Date Range - 02.02.24 to 05.07.24, then the user must see the Previous week counts on one card (1 week ago) and Previous to Previous Week counts on another card (2week ago) . So currently it just considers the offset in the measure but it has to interact with date range slicer.

      • tharunkumarRTK's avatar
        tharunkumarRTK
        Icon for Super User rankSuper User
        2 years ago

        Anonymous 
        If I understand your requirement correctly then finding the max week offset value within the selected date range and passing weekOffset - 1 and weekOffser - 2 filters for previous week and previous to previous week should work. If you still need any help then please share the pbix file with sample data.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    According to your statement, I think you want to calculate the number of previous week and the number of 2nd previous week based on the last date in your slicer.

    Here I suggest you to try code as below. My Sample is easy a Data table with two columns [Date] and [Number] and a Calendar Table with date from 2023/08/28 to today.

    Previous Month Number = 
VAR _MAXDATE = MAX('Calendar'[Date])
VAR _PREVIOUSWEEKEND = _MAXDATE - WEEKDAY(_MAXDATE,2)
VAR _PREVIOUSWEEKSTART = _PREVIOUSWEEKEND - 6
RETURN
CALCULATE(SUM('Table'[Number]),FILTER(ALL('Calendar'),'Calendar'[Date]>=_PREVIOUSWEEKSTART && 'Calendar'[Date]<=_PREVIOUSWEEKEND))
    2nd Previous Month Number = 
VAR _MAXDATE = MAX('Calendar'[Date])
VAR _PREVIOUSWEEKEND = _MAXDATE - WEEKDAY(_MAXDATE,2)
VAR _2NDPREVIOUSWEEKEND = _PREVIOUSWEEKEND - 7
VAR _2NDPREVIOUSSTART = _2NDPREVIOUSWEEKEND - 6
RETURN
CALCULATE(SUM('Table'[Number]),FILTER(ALL('Calendar'),'Calendar'[Date]>=_2NDPREVIOUSSTART && 'Calendar'[Date]<=_2NDPREVIOUSWEEKEND))
    Difference = [Previous Month Number] - [2nd Previous Month Number]
    Difference% = DIVIDE([Difference],[2nd Previous Month Number])

    Result is as below.

    If this reply still couldn't help you solve your issue, please share a sample file with us.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.