Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Getting data for last week Monday - Sunday in Powerbi

Hi, I have to display count of custid's based on Purchase date for last week (monday -Sunday) and the week Previous to that (Previous monday - Sunday of last week) in matrix visual.

 

Can someone please suggest the best way to achieve this ?

 

Many Thanks

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , please do necessary modifications

     

    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1


    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)


    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)




     

    measures
    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
    Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
    last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1
    && 'Date'[Week Rank]>=max('Date'[Week Rank])-3))

11 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Arun, but when i copy paste your DAX for creating the Date table, its throwing errors. can you please advice ? Thanks

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        Anonymous , please do necessary modifications

         

        new columns
        Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1


        Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)


        Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)




         

        measures
        This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
        Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
        Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
        Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
        last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1
        && 'Date'[Week Rank]>=max('Date'[Week Rank])-3))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Sorry, i mean Amit

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit, its not allowing me to create any new columns in the new date table . its throwing error saying column Date cannot be found in table Date. this is correct as we dont have any column name date right ? please advice.

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit, while creating the new columns in the date table, it says date column not availabe in the date table. 

    this is true right ? we dont have date column in this table as its new table right ? can you please advice ? Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Amit, I think I need to create the date column first right ? can i use the below dax first to create the date column for my date table and then create the remaining 3 columns which you suggested ?

     

    Date = CALENDAR (DATE (1901, 1, 1), DATE (2099, 12, 31))

     

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit, can you please update me your week rank according to my requirement ? my report should only display data for last week Monday-Sunday for current and the previous week Monday -Sunday data for previous.

     

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

     

    Many Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit I finally worked this out without using the RANK. Below are my meaures.

     

    CurrentReservations =
    VAR PrevSunday = TODAY() - WEEKDAY(TODAY(),2)
    VAR PrevMonday = PrevSunday - 6
    RETURN CALCULATE (COUNTROWS('Sample') , FILTER(ALL('Date') , 'Date'[Date] >= PrevMonday && 'Date'[Date] <= PrevSunday))

     

    PrevReservations =
    VAR PrevPrevSunday = TODAY() - WEEKDAY(TODAY(),2) - 7
    VAR PrevPrevMonday = PrevPrevSunday - 6
    RETURN CALCULATE (COUNTROWS('Sample') , FILTER(ALL('Date') , 'Date'[Date] >= PrevPrevMonday && 'Date'[Date] <= PrevPrevSunday))

     

    Many Thanks for your help and guidance so far. Much appricated Amit