Unable to get correct values when using different date format column from same table

[theDarkPrince]

Hi everyone. This one's got me stumped. Below is a screenshot that depicts my connections in the Data Model:

The YYYY_MM column has format "2021-09" and MONTH_IN_CAL column has format "Sep-21".

I have created a measure as following:

Previous Month Backlog = 
var curr_mon_yyyy_mm = "2021-09"
var prev_mon_yyyy_mm = "2021-08"
var Result = IF( MIN('Calendar'[YYYY_MM]) = curr_mon_yyyy_mm, CALCULATE(SUM('Facts Data'[BACKLOG_AMOUNT]), 'Calendar'[YYYY_MM] = prev_mon_yyyy_mm ), BLANK() )
Return Result

What I am trying to achieve in this measure is computing the amount for previous month and display it in current month as below:

With the YYYY_MM column, it works just fine and I get correct amount. However, I want to achieve the same with MONTH_IN_CAL column but am getting blank:

Both YYYY_MM & MONTH_IN_CAL come from the same Calendar table. To try and get to the bottom of things, I obtained the DAX Query using Performance Analyzer and got this:

DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('Calendar'[MONTH_IN_CAL], 'Calendar'[YYYY_MM]), "IsGrandTotalRowTotal"
      ),
      "Previous_Month_Backlog", 'Facts Data'[Previous Month Backlog]
    )
  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'Calendar'[YYYY_MM],
      1,
      'Calendar'[MONTH_IN_CAL],
      1
    )
EVALUATE
  __DS0PrimaryWindowed
ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Calendar'[YYYY_MM], 'Calendar'[MONTH_IN_CAL]

YYYY_MM by itself is ordered but to show MONTH_IN_CAL in proper chronological order, I have sorted it by the YYYY_MM column.

Therefore, it seems, even though the YYYY_MM column is not being used in the table visual, it'll automatically become part of the DAX Query for ordering purposes. I did a little bit of digging around how I can solve this and found this https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-us... article. However, the author suggests using ALL('Calendar'[MONTH_IN_CAL], 'Calendar'[YYYY_MM] ), but using that gives me incorrect number which is a total of all values in the table. Any ideas about how this can be resolved. Any help is appreciated. Thanks.

@amitchandak @Greg_Deckler @parry2k @ryan_mayu @v-rzhou-msft 

[Greg_Deckler]

@theDarkPrince Thinking:

Previous Month Backlog Measure = 
var __Current = MAX('Calendar'[Date])
VAR __PreviousEOMonth = EOMONTH(__Current,-1)
VAR __Previous = DATE(YEAR(__PreviousEOMOnth),MONTH(__PreviousEOMOnth),1)
var Result = CALCULATE(SUM('Facts Data'[BACKLOG_AMOUNT]), 'Calendar'[Date] <= __Previous, 'Calendar'[Date]<=__PreviousEOMonth)
Return Result

[amitchandak · Accepted solution]

@theDarkPrince , Create a rank in Calendar table on YYYYMM

new column

Month Rank = RANKX(all('Date'),'Date'[YYYY_MM],,ASC,Dense)

example 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))

Last Year Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month]=max(Period[Month]) && 'Date'[Year]=max('Date'[Year])-1))