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 defaul...
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.

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.