Forum Discussion

mayurwadhwani's avatar
mayurwadhwani
Helper I
5 years ago
Solved

DAX Measure for Date Problem

Hello Experts, I have a table named Members and below is the data: 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 th...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

  • mayurwadhwani's avatar
    mayurwadhwani
    Helper I
    5 years ago

    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's avatar
    mayurwadhwani
    Helper I
    5 years ago

    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