DAX Calculation for events that happens in the same month as another event

[Femi10]

Good evening all,

Below is the structure of my data. I want to run a report that counts the number of invoices that were invoiced and completed in the same calendar month.

Please how do I go about it?

Thank you 

@smpa01

@Greg_Deckler
@AlexisOlson

@amitchandak

[smpa01]

@Femi10  I think the following will give you what you need and you would not need a Calendar Table at all.

Measure = 
COUNTROWS (
    FILTER (
        tbl,
        YEAR ( tbl[invoice Date] ) = YEAR ( tbl[Completed Date] )
            && MONTH ( tbl[invoice Date] ) = MONTH ( tbl[Completed Date] )
    )
)

[smpa01]

@Femi10  did you have a chance to look into the solution I provided?

[Femi10]

Hi 

Thank you so much, I just checked it a few minutes ago. It still didn't provide the correct answer.  

However, I found another way to go about it which counted the monthly columns correctly,  the problem with it is that it's giving me a wrong column total.

CALCULATE([DistinctJobCount],

DATESBETWEEN('Payment Count'[Completed Date], STARTOFMONTH('Date'[Date]), ENDOFMONTH('Date'[Date].[Date])))

Here is a screenshot of my visual. "Me" is the DAX I wrote above(with the wrong total) and SMPA is the Dax you sent to me.
Mine is the correct one

Could you please show me how I would approach it if I wanted to do a distinct count rather than a normal count?

[smpa01]

@Femi10  my solution was based on the data that you provided and you can see that it works.

If the sample data is not represntative of the issue and if you hvae not provided other details, please post the pbix reprentative of your issue and someone will look into it.

[Femi10]

@smpa01 Thank so much for the help. It is well appreciated

[smpa01]

@Femi10  one way of achieving, with Calendar Table, pbix is attached

Measure5 = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS ( tbl, "invDetails", RELATED ( 'Calendar'[Year-Month] ) ),
            "completedDetails",
                CALCULATE (
                    CALCULATE (
                        MAXX ( 'Calendar', 'Calendar'[Year-Month] ),
                        TREATAS ( VALUES ( tbl[Completed Date] ), 'Calendar'[Date] )
                    )
                )
        ),
        [invDetails] = [completedDetails]
    )
)

[Femi10]

Thank you

It didn't quite work, I suspect this might be connected to the introduction of the Calendar Table.

Can you please give a brief explanation of your DAX, that could help me find my way around it.

What do I change if I want to count distinct invoice numbers? (some invoice numbers there appeared more than once)

Many thanks