Forum Discussion
DAX Measure for Date Problem
Hello Experts,
I have a table named Members and below is the data:
Members Table
I want to get distinct count of pkey year wise. If a pkey is active from 2019 to 9999 or till 2019 or greater than 2019 then that pkey will be counted for year 2019 (For startDate 2019 we need to consider records having startdate year as 2019 or less than 2019).
Also I have DateDim (created using dax) for year 2018,2019,2020 and 2021. In my model there is no relationship defined between DateDim and Members table.
Also we have Year slicer in report which we want to pull from DateDim and based on the slicer selection grid should change.
Below is the expected output:
Expected Output
Can we achieve this using DAX measure?
Any help or suggestion would highly be appreciated.
Thanks
Hi mayurwadhwani ,
I updated the formula in the original sample pbix file, please check whether it is ok.
Measure = VAR _count = COUNTROWS ( ALLSELECTED ( 'DateDim'[Date].[Year] ) ) VAR _count1 = CALCULATE ( DISTINCTCOUNT ( 'DateDim'[Date].[Year] ), ALL ( 'DateDim' ) ) RETURN IF ( _count = _count1, CALCULATE ( DISTINCTCOUNT ( 'Members'[pkey] ) ), CALCULATE ( [Total Count distinct pkey], FILTER ( ALLSELECTED ( 'DateDim' ), 'DateDim'[Date] = MAX ( 'DateDim'[Date] ) ) ) )Best Regards
8 Replies
Thanks amitchandak for the response. My problem statement seems bit different. Its not like counting days between start date and endate. It also including comparision with entire table.
Any other suggerstions to achieve expected output.Thanks
Thanks amitchandak for the response. My problem statement seems bit different. Its not like counting days between start date and endate. It also including comparision with entire table.
Any other suggerstions to achieve expected output.Thanks
Hi mayurwadhwani ,
You can create a measure as below:
Total Count distinct pkey = VAR _selyear = SELECTEDVALUE ( 'DateDim'[Date].[Year] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Members'[pkey] ), FILTER ( 'Members', YEAR ( 'Members'[StartDate] ) <= _selyear && YEAR ( 'Members'[EndDate] ) >= _selyear ) )Best Regards
Thanks Anonymous for the response. But if we keep card visual and show total members irrespective of year. And if we select multiple year from slicer, it shows incorrect value. As SELECTEDVALUE function results blank with multiple selection of year slicer.
Tried to solve this using VALUES function but no luck.Can we achieve this using DAX?
Thanks
Hi mayurwadhwani ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
