Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX tips

Hi, Power Bi Community.  I have a question on DAX. I have the following table.      I wan t to substract  week 202150 - 6.25% - week 202149-6.82%.  NOTE: %TOTAL_BLOCKED_UNITS_CURRENT is a ...
  • amitchandak's avatar
    4 years ago

    Anonymous , Wih help from date with week rank or week table with Week Rank on Year Week /YYYYWW

     

    You can compare this week vs Last week, if that is needed as a measures

     

    new columns in date/week table
    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)

    OR

     

    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    example measures for WOW
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))