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?

Rupak_bi's avatar
Rupak_bi
Icon for Super User rankSuper User
1 year ago

Hi Metacomet ,

Try this

Products Previous Month =
CALCULATE(
   DISTINCTCOUNT(
        'Table'[PRODUCT]),
        FILTER(all('Table'), PREVIOUSMONTH('Table'[DATE])
        )
)
  • Metacomet's avatar
    Metacomet
    Regular Visitor
    1 year ago

    Thank you for your reply. Unfortunately, this solution always returns the same value, no matter what month I choose.