DAX how do I compare two date columns and count the values if the month is the same using a slicer

[StevenHarrison]

Hi, Please can you help me with the following?

In DAX how do I compare two date columns and count the values if the month is the same, whilst ignoring blank or null values in the second column, using the SELECTEDVALUE statement from a slicer using the first column of dates?

I have tried the following DAX but just get a blank value being returned:

Stage2Count Fiscal Year =

    VAR SelectedDate = SELECTEDVALUE('Complaint (rk_complaint)'[c_DateReceived])

    RETURN

        COUNTROWS(

            FILTER(

                'Complaint (rk_complaint)',

                NOT(ISBLANK('Complaint (rk_complaint)'[Stage2StartDate])) &&

                MONTH('Complaint (rk_complaint)'[Stage2StartDate]) = MONTH(SelectedDate) &&

                YEAR('Complaint (rk_complaint)'[Stage2StartDate]) = YEAR(SelectedDate)

            )

        )

Column example unfiltered:

Using the c_DateReceived date column as the slicer:

using a simple count on the Stage2StartDate, I get a value of 1 but the measure returns a blank value:

Thank you in advance.

[v-linyulu-msft]

Hi,@StevenHarrison 

First of all, as I understand it, your request is to return the number of rows with values that are the same as the year of the month of the selected value, based on the value of the slicer selected in the first column. If my understanding is wrong, please give your relative opinion:

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.I then created the following measure:

Stage2Count Fiscal Year = 
VAR month1 = SELECTEDVALUE('Complaint (rk_complaint)'[c_DateReceived].[Month])
VAR year1 = SELECTEDVALUE('Complaint (rk_complaint)'[c_DateReceived].[Year])

var cow= CALCULATE(COUNTROWS('Complaint (rk_complaint)'),
        FILTER(
            'Complaint (rk_complaint)',
            NOT(ISBLANK('Complaint (rk_complaint)'[Stage2StartDate])) &&
            'Complaint (rk_complaint)'[Stage2StartDate].[Month]= month1 &&
            'Complaint (rk_complaint)'[Stage2StartDate].[Year]= year1
        )
    )
RETURN cow

3.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

[StevenHarrison]

Hi @v-linyulu-msft , thank you for the reply

Please see screenshot below:

what I am after is, if you select a month from the slicer it uses the dates in the first column (c_DateReceived field) , but I want to count the Stage 2 entries, so in this case, if I select March 2023 I want to count that entry.

So eventhough c_DateReceived is 23/11/2023 and Stage2StartDate is 13/03/2023, I want to be able to select March 2023 and it return a count of 1 or however many there are, if that makes sense.

I managed to get somewhere with the following, but it only brings back the month number, so need to count the values not return the month number:

Matching dateszzzzz =

CALCULATE (

    MAX ( 'Complaint (rk_complaint)'[Stage2StartDate].[MonthNo]),

    FILTER (

        ALL ( 'Complaint (rk_complaint)'),

        'Complaint (rk_complaint)'[Stage2StartDate].[Month] = SELECTEDVALUE ( 'Complaint (rk_complaint)'[c_DateReceived].[Month] ) &&

        'Complaint (rk_complaint)'[Stage2StartDate].[Year] = SELECTEDVALUE ( 'Complaint (rk_complaint)'[c_DateReceived].[Year] )

    )

)

[v-linyulu-msft]

Hi,@StevenHarrison 

First of all, according to your presentation, I find that there is a mistake in the difficulty of communication between us. From the chart you gave me, I understand that your request is that you want March 2024 to be counted in the second column, but in your presentation it is March 2023, so if I am wrong in my understanding, could you let me know in the form of a picture?

For your request, my understanding is that you want to look up the c_DateReceived column with the same value as its month in the Stage2StartDate column with the month already selected by the slicer and count his total.

1. Based on my understanding of your requirements, I have created the following measure:

Matching dateszzzzz = 
 
var mm=CALCULATE (
    MAX ( 'Complaint (rk_complaint)'[Stage2StartDate].[MonthNo]),
    FILTER (
        ALL ( 'Complaint (rk_complaint)'),
        'Complaint (rk_complaint)'[Stage2StartDate].[Month] = SELECTEDVALUE ( 'Complaint (rk_complaint)'[c_DateReceived].[Month] ) &&
        'Complaint (rk_complaint)'[Stage2StartDate].[Year] = SELECTEDVALUE ( 'Complaint (rk_complaint)'[c_DateReceived].[Year] )
    )
)
RETURN CALCULATE(COUNTROWS('Complaint (rk_complaint)'),FILTER('Complaint (rk_complaint)','Complaint (rk_complaint)'[c_DateReceived].[MonthNo]=mm))

2.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you. To make it easier for me to understand your needs, you can also communicate your needs to me in a drawing.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StevenHarrison]

Hi @v-linyulu-msft , First of all please accept my apologies, you're right the date should have been March 2024, I think I had got to a place where I couldn't see the wood for the trees :).

From your pictures the count is 8 but what I need is the count to be 1 as there is only one stage 2 date in the list.

also if the two dates are in different months as the sceenshot below, I would need to count the Stage2StartDate column when selecting March 2024 (eventhough the slicer uses the c_DateReceived column)

[StevenHarrison · Accepted solution]

Hi @v-linyulu-msft , The simplest way around this problem was to create a duplicate table and filter on Stage 2 dates having a value and creating a separate Date table to link them.

The only reason I didn't do this to start with was the stakeholders requirements didn't high light this as a report parameter to begin with, only through use of the report did it arise.

Thank you for your help on this.