Search code examples
powerbipowerbi-desktopdaxstudiotabular-editor

Tabular Editor Time Intelligence Calculation Groups (Previous Month and Last 12 Months not displaying Correctly)

The main problem I am running into is that I cannot display both the previous month and last 12 months at the same time on a visual using Tabular Editor Calculation Groups. Current_DT is the Primary Date Column on the Date Table. See Tabular Editor Calcs below.

Previous Month

CALCULATE (
    SELECTEDMEASURE (),
    PREVIOUSMONTH ( 'Dim_Date'[Current_DT] )
)

Last 12 Months

CALCULATE (
    SELECTEDMEASURE (),
    SAMEPERIODLASTYEAR ( 'Dim_Date'[Current_DT] )
)

I opted for a tabular editor calculation group method. In order to create a date context on the visual, I created another column on the date table that creates a flag on whether or not the row date is Today (Yes), After Today (After), Before today (Before). On the visual filter if I only select Today only the Previous Month displays. If I select only Before today, only Last 12 months displays correctly. If I select both then only last 12 months displays correctly and Previous Month is Blank.

Nearly all visuals will have the same time intelligence ranges so It is important that we use tabular editor to save time. Instead of creating tons of different measures in DAX directly.

Solution

  • The issue with PREVIOUSMONTH is that it uses the first date in the current context to get the previous month of that.

    They the following:

    Previous Month = 
  CALCULATE (
    SELECTEDMEASURE (),
    PREVIOUSMONTH ( DATESMTD('Dim_Date'[Current_DT]) )
  )