Forum Discussion

HEW's avatar
HEW
Icon for Helper III rankHelper 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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
      Icon for Helper III rankHelper 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
        Icon for Community Support rankCommunity 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.