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

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