Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
prv_1401
Helper I
Helper I

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

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.
prv_1401_0-1698324475879.png

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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

vyangliumsft_0-1698732998116.png

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:

vyangliumsft_1-1698732998118.png

 

 

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

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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

vyangliumsft_0-1698732998116.png

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:

vyangliumsft_1-1698732998118.png

 

 

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
Community Champion
Community Champion

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
Super User
Super User

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

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



prv_1401_0-1698326628709.png

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.