Forum Discussion

HEW's avatar
HEW
Helper III
4 years ago
Solved

Show last 4 weeks in Matrix

Hi.   I would like to show a matrix, where values for only the last 4 weeks are shown. Now it is week 1, so I would like to see data for week 49-52. How do I solve this? Thanks a lot.
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi HEW ,

     

    Please try the following formula:

     

    Measure = 
var CurWeekDay = WEEKDAY(TODAY(), 2)
var DateEnd = TODAY() - CurWeekDay
var DateStart = DateEnd - 7 * 4 + 1
return 
    CALCULATE( 
        SUM('Table'[Value]) ,
        FILTER(
            Dates,
            Dates[Date] >= DateStart 
                && Dates[Date] <= DateEnd
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Super User
4 years ago

HEW , I need based on today use relative date slicer.

Last 4 week based on max date in table no date selected

Last 4 weeks =
var _max1 = maxx(allselected('Table'), 'Table'[date])
var _max = maxx(filter(all(Date), Date[date] = _max1), Date[Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-4 && 'Date'[Week Rank]<=_max))

 

But if you want to select a date and then want to show the last 4 week you need an independent table

 

//Date1 is independent Date table, Date is joined with Table

Last 4 weeks =
var _max1 = maxx(allselected('Date1'), 'Date1'[date])
var _max = maxx(filter(all(Date1), date[Date1] = _max1), Date1[Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-4 && 'Date'[Week Rank]<=_max))

 

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

  • HEW's avatar
    HEW
    Helper III
    4 years ago

    Thanks a lot. 

    If I dont want to select a data but automatically want the last 4 weeks to be shown (based on today) and not including current week, should I use Today instead of rank?

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 years ago

      Hi HEW ,

       

      Please try the following formula:

       

      Measure = 
var CurWeekDay = WEEKDAY(TODAY(), 2)
var DateEnd = TODAY() - CurWeekDay
var DateStart = DateEnd - 7 * 4 + 1
return 
    CALCULATE( 
        SUM('Table'[Value]) ,
        FILTER(
            Dates,
            Dates[Date] >= DateStart 
                && Dates[Date] <= DateEnd
        )
    )

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.