topic Re: Calculating data for previous month with selected date in DAX Commands and Tips

Calculating data for previous month with selected date

Metacomet — Tue, 03 Dec 2024 23:57:42 GMT

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) ) )

Re: Calculating data for previous month with selected date

Rupak_bi — Wed, 04 Dec 2024 06:44:03 GMT

Hi Metacomet ,

Try this

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

Re: Calculating data for previous month with selected date

moncx — Wed, 04 Dec 2024 07:45:49 GMT

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.

Re: Calculating data for previous month with selected date

Anonymous — Thu, 05 Dec 2024 05:52:10 GMT

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.

Re: Calculating data for previous month with selected date

Metacomet — Fri, 06 Dec 2024 11:35:39 GMT

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?

Re: Calculating data for previous month with selected date

Metacomet — Fri, 06 Dec 2024 10:56:34 GMT

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

Re: Calculating data for previous month with selected date

Metacomet — Fri, 06 Dec 2024 11:15:55 GMT

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.

Re: Calculating data for previous month with selected date

Metacomet — Fri, 06 Dec 2024 11:16:53 GMT

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

Re: Calculating data for previous month with selected date

Anonymous — Wed, 11 Dec 2024 09:04:16 GMT

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