Filtering/finding most common month & week with a Date/Time Format

[MichaiahGartner]

I have a table that lists invoices along with a date column that is formatted like 12/12/2012 12:00:00 AM. I want to find (through DAX) the most frequently occuring month and week but am now sure how. 

[Ashish_Mathur · Accepted solution]

Hi,

Try this

1. Create a Calendar Table with the following calculated colums to extract Month and Week: Month=FORMAT(Calendar[Date],"mmmm") and Week=WEEKNUM(Calendar[Date])
2. Create a relationship from the Date column of your Data Table to the Date column of your Calendar Table
3. Write this measure

Invoice count=COUNTA(Data[Invoice Number])

You may sort the measure in descending order.

[v-frfei-msft · Accepted solution]

Hi @MichaiahGartner,

As davehus metioned, I made one sample for your reference.

1. Enter the sample data and create some calculated columns in the table.

Month = FORMAT(Invoice[Date],"mmmm")

weeknum = WEEKNUM(Invoice[Date])

2. Create the measures to achieve your goal.

Invoice Count Measure = COUNT(Invoice[Month])

Top Month = 
IF (
    NOT ( ISBLANK ( Invoice[Invoice Count Measure] ) ),
    MAXX (
        TOPN (
            1,
            SUMMARIZE (
                Invoice,
                Invoice[Month],
                "Invoice Count", Invoice[Invoice Count Measure]
            ),
            [Invoice Count]
        ),
        Invoice[Month]
    )
)

Top week = 
IF (
    NOT ( ISBLANK ( Invoice[Invoice Count Measure] ) ),
    MAXX (
        TOPN (
            1,
            SUMMARIZE (
                Invoice,
                Invoice[weeknum],
                "Invoice Count", Invoice[Invoice Count Measure]
            ),
            [Invoice Count]
        ),
        Invoice[weeknum]
    )
)

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @MichaiahGartner,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[Ashish_Mathur · Accepted solution]

Hi,

Try this

1. Create a Calendar Table with the following calculated colums to extract Month and Week: Month=FORMAT(Calendar[Date],"mmmm") and Week=WEEKNUM(Calendar[Date])
2. Create a relationship from the Date column of your Data Table to the Date column of your Calendar Table
3. Write this measure

Invoice count=COUNTA(Data[Invoice Number])

You may sort the measure in descending order.

[davehus]

Top Month = 

IF (
    NOT ( ISBLANK ( "Invoice Count Measure" ) ),
    MAXX (
        TOPN (
            1,
            SUMMARIZE (
                'Your Table',
                'Date Table'[Month Name],
                "Invoice Count", "Invoice Count Measure"
            ),
            [Invoice Count]
        ),
        'Date Table'[Month Name]
    )
)

Hi - Please see the measure I've included for you. I'm assuming that you have a date table linked to the table and the measure is written with this in mind. 

Hopefull this helps.

D