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?

Metacomet's avatar
Metacomet
Regular Visitor
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?

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Metacomet ,

Is this issue solved now? If you have any questions, please feel free to share with us.

Could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. And what you expect the output to be. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best regards,

Lucy Chen