Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Same Week Previous Month

Hi Expert   I am using Direct Query and trying to work out the same week previous month in Power BI. In my DimDate table i have Date, Year, WeekNum but not week ranking column.    How would you d...
  • amitchandak's avatar
    4 years ago

    Anonymous , You need new columns

     

     

    Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
    Month Day = DATEDIFF('Date'[Month Start Date],'Date'[Date],Day)+1
    min week start of month = minx(filter('Date',[Month Year] =earlier([Month Year])),[Week Start date])
    week of month = datediff([min week start of month],[date],Week)+1

     

    Measure
    This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
    Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
    MTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [week of month] <=max([week of month])))
    LMTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [week of month] <=max([week of month])))