topic Dax to calculate distinctcount of ID of previous month and current month based on filter selection. in DAX Commands and Tips

Dax to calculate distinctcount of ID of previous month and current month based on filter selection.

prv_1401 — Thu, 26 Oct 2023 12:58:16 GMT

Hi everyone, I want to calculate distinct count of product ID based on the filter selection of date.
Lets say, I select month Sept in the date slicer. I want to show the distinct count of products of the selected month and the previous month of it. And it has to be filtered with status A only. Which I can add it in the filter pane.
I wrote the following dax but I am not able to get the result.

XYZ =

VAR A = SELECTEDVALUE('r Data'[date])

RETURN

CALCULATE(

DISTINCTCOUNT(' Data'[product_id]),

FILTER(' Data', ' Data'[date] = A && 'Master Data'[date] = A - 1)

)

I have attached the data as well for the reference. Date format is same as it is here.

Please suggest an alternative way or share your thoughts on this. Thank you
123abc lbendlin

Re: Dax to calculate distinctcount of ID of previous month and current month based on filter selection.

lbendlin — Thu, 26 Oct 2023 13:04:55 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Dax to calculate distinctcount of ID of previous month and current month based on filter selection.

123abc — Thu, 26 Oct 2023 13:21:17 GMT

You can achieve this by modifying your DAX calculation as follows:

XYZ =
VAR SelectedDate = SELECTEDVALUE('r Data'[date])
VAR PreviousMonth = SelectedDate - 1
RETURN
CALCULATE(
DISTINCTCOUNT(' Data'[product_id]),
FILTER(' Data', ' Data'[date] = SelectedDate || ' Data'[date] = PreviousMonth),
' Data'[status] = "A"
)

Re: Dax to calculate distinctcount of ID of previous month and current month based on filter selection.

prv_1401 — Thu, 26 Oct 2023 13:32:29 GMT

Hi lbendlin , this is sample data with output. Can you please check the dax and let me know whats the issue in it ?

Re: Dax to calculate distinctcount of ID of previous month and current month based on filter selection.

lbendlin — Thu, 26 Oct 2023 13:41:59 GMT

second request: Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Dax to calculate distinctcount of ID of previous month and current month based on filter selection.

Anonymous — Tue, 31 Oct 2023 06:17:28 GMT

Hi prv_1401 ,

Here are the steps you can follow：

1. Create calculated table.

Date = var _table1= CALENDAR( DATE(2001,1,1), DATE(2001,12,31)) return ADDCOLUMNS( _table1,"Date_format",FORMAT([Date],"yy")&"-"&FORMAT([Date],"mmm") )

2. Create measure.

Measure = var _selectdate=SELECTEDVALUE('Date'[Date_format]) var _selectstatus=SELECTEDVALUE('Master Data'[Status]) var _date1=MINX(FILTER(ALL('Date'),'Date'[Date_format]=_selectdate),[Date]) var _date2=DATE(YEAR(_date1),MONTH(_date1)-1,DAY(_date1)) var _lastmonth=MONTH(_date2) var _currentmonth=MONTH(_date1) return CALCULATE( DISTINCTCOUNT('Master Data'[Product ID]), FILTER(ALL('Master Data'), 'Master Data'[Month ID] in {_lastmonth,_currentmonth} &&'Master Data'[Status]=_selectstatus))

3. Result:

Best Regards,

Liu Yang

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