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

[prv_1401]

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 

[v-yangliu-msft · Accepted solution]

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

[123abc]

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

[lbendlin]

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-...
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/1447...

[prv_1401]

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

[lbendlin]

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-...
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/1447...