Calculating data for previous month with selected date

[Metacomet]

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 all products for the selected month,
- The second one should calculate all products for the previous months.

Without a slicer, this code works perfectly:

Products Previous Month =
CALCULATE(
   DISTINCTCOUNT(
        'Table'[PRODUCT]),
        FILTER('Table', PREVIOUSMONTH('Table'[DATE])
        )
)

But I can't get it to work with the slicer. Whenever I choose a month, I get the same number on both cards.

I have searched for answers and found some solutions and hints, but for some reason, all calculations return the same amount for the selected and previous month.
Here is an example of one of the solutions I found:

Products Previous Month =
VAR _SelectedMonth = SELECTEDVALUE('Table'[DATE])
VAR _diff = DATEDIFF(_SelectedMonth, TODAY(), MONTH) +1
RETURN
CALCULATE(
   DISTINCTCOUNT(
        'Table'[PRODUCT]),
        FILTER('Table', DATEADD('Table'[DATE], -_diff, MONTH)
        )
)

[Metacomet · Accepted solution]

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?

[v-xinc-msft]

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

[v-xinc-msft]

Hi @Metacomet ,

Please follow the steps below.

1. Create a Date Table, add new columns to calculate the years and months in the table.

 

2. Manage a new relationship.

3. Create the first measure to calculate all products for the selected month.

Selected Month Products = 
CALCULATE(
    DISTINCTCOUNT(
        'Table'[Product]),
        ALLSELECTED(
            'Date'[Year],'Date'[Month])
            )

4. Create the second measure to calculate all products for the previous months.

Products Previous Month = 
Var _minmonth=CALCULATE(MIN('Date'[Month]),ALLSELECTED('Date'[Month]))
RETURN
CALCULATE(
    DISTINCTCOUNT(
        'Table'[Product]),
        FILTER(
            ALL('Table'),
            [Date]<=EOMONTH(DATE(SELECTEDVALUE('Date'[Year]),_minmonth,1),-1)
            )
)

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Metacomet]

Thank you for your answer. I tried to apply it to my data, but I have a many-to-one relationship, and this is not working with your solution. I downloaded your solution and changed its relationship to many-to-one, and it stopped working as well. Sorry.

[moncx]

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]

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

[Rupak_bi]

Hi @Metacomet ,

Try this

Products Previous Month =
CALCULATE(
   DISTINCTCOUNT(
        'Table'[PRODUCT]),
        FILTER(all('Table'), PREVIOUSMONTH('Table'[DATE])
        )
)

[Metacomet]

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