DAX Measure for Date Problem

[mayurwadhwani]

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

[Anonymous]

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

[mayurwadhwani]

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

[Anonymous]

Hi @mayurwadhwani ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

Best Regards

[mayurwadhwani]

Hi @Anonymous ,

If we select all the year in slicer it should show as 8 distinct key. In pbix, its coming out to be 4. Its due to SELECTEDVALUE function. As multiple selection is returning blank.

Thanks

[Anonymous · Accepted solution]

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

[amitchandak]

@mayurwadhwani , refer if the blog or attcahed file can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[mayurwadhwani]

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

[mayurwadhwani]

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