DAX function to count total number of reference per month i receive

[Anonymous]

To explain more clear my need, I have a sharepoint table called : "Liste receuil de consultation". I imported this table to power BI. In this table i have more than 10 columns. Below, we have two column of those more than 10. In the first column, we have reference number and in the second one, arrival date (the date we receive each reference). What i just want to do is, to visualise, the number of refence we receive per month and compare then. In "juin" for example we 2 references, in "Août", we receive 3. In "Sept", we receive 2 . etc....

Month	# of reference
June	2
August	3
Sept	2
Total	7

[bcdobbs · Accepted solution]

Hi,

I'll send a demo file over later this evening. However in brief:

1) Create a date table.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

2) Link it to your date field.

3) You could then just drop reference id into a matrix with month from your date table and tell power bi to count. However a better way is to create a measure like:

Number of References = COUNTROWS( ReferenceTableName )

If you have more than one row per reference use

Number of References = DISTINCTCOUNT(ReferenceTableName[ReferenceId]

[bcdobbs · Accepted solution]

Hi,

I'll send a demo file over later this evening. However in brief:

1) Create a date table.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

2) Link it to your date field.

3) You could then just drop reference id into a matrix with month from your date table and tell power bi to count. However a better way is to create a measure like:

Number of References = COUNTROWS( ReferenceTableName )

If you have more than one row per reference use

Number of References = DISTINCTCOUNT(ReferenceTableName[ReferenceId]

[Anonymous]

Hi

Thanks for your reply. I will try your solution and will be waiting for the demo as well.

Best regards

[bcdobbs · Accepted solution]

Demo File 

DAX for Calendar table:

Dates = 
    VAR EarliestDate = MIN ( Reference[Date Arrivee] )
    VAR LatestDate = MAX ( Reference[Date Arrivee] )

    RETURN 
    ADDCOLUMNS(
        CALENDAR( 
            DATE ( YEAR( EarliestDate ), 1, 1 ), 
            DATE ( YEAR ( LatestDate ), 12, 31 )
        ),
    "Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) // Format as mmmm yyyy
    )

DAX for Measure:

Number of References = COUNTROWS ( Reference )

[Anonymous]

Hi

Thank you a lot. It's working perfectly.

Regards

[bcdobbs]

Glad it's working. If you could mark as a solution I'd appreciate.