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 p...
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))

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