Forum Discussion

Metacomet's avatar
Metacomet
Regular Visitor
1 year ago
Solved

Calculating data for previous month with selected date

Hi, I have two columns: PRODUCT and DATE. On a canvas, there is a slicer, where I can choose any month and year for the last three years. Also, there are two cards: - The first one is calculating...
  • Metacomet's avatar
    Metacomet
    1 year ago

    I have found a solution and I'm sharing it if anyone else needs it in the future:

    VAR _PreviousMonthDate = MONTH(PREVIOUSMONTH('TABLE'[DATE]))
VAR _PreviousYearDate = IF(_PreviousMonthDate = 12, YEAR(SELECTEDVALUE('TABLE'[DATE]))-1, YEAR(SELECTEDVALUE('TABLE'[DATE])))
RETURN
CALCULATE(
    DISTINCTCOUNT('TABLE'[PRODUCT]),
        FILTER(
            ALL('TABLE'[DATE]),
                YEAR('TABLE'[DATE]) = _PreviousYearDate
                &&
                MONTH('TABLE'[DATE] = _PreviousMonthDate
            )

)

     

    However, while trying many, many solutions, I broke somehow (no idea how) hierarchy in the [DATE] column, and suddenly the first calculation I created started to work:

    CALCULATE(
    DISTINCTCOUNT('TABLE'[PRODUCT]), PREVIOUSMONTH('TABLE'[DATE])
)

    But I can't have a broken hierarchy, so I had to revert it.

    As I would like to understand this, does anyone know why this is creating problems for the calculations?

moncx's avatar
moncx
Icon for Resolver II rankResolver II
1 year ago

Hey,

create separate Date table in your model, because if dates in your fact table are not full then PREVIOUSMONTH functions gets confused and does not return correct answer. You can create Date table with DAX like this:

Date =
VAR MinYear = YEAR ( MIN ( 'Table'[DATE] ) )
VAR MaxYear = YEAR ( MAX ( 'Table'[DATE] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] )
)

Then connect in your model Date table field [Date] with your Table field [Date] and use Date table in your slicer and measure for calculating previous month. For previous month DAX looks like this:
Products Previous Month =CALCULATE([Products Current Month], PREVIOUSMONTH('Date'[Date]))

It is always better to create Date table when you do calendar/date calculations, because the functions work best then because you have table with all the dates and calculations can be performed correctly then.
  • Metacomet's avatar
    Metacomet
    Regular Visitor
    1 year ago

    Thank you for your advice. I tried this solution, but it always returns BLANKs.