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

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.

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