Display months based on days

[tsak]

How can I display months based on the first Monday of each month??

For example August 21 should be from 02/08/2021 - 05/09/2021

I have calculated the iso week based on WEEKNUM([Date],21), so what I want basically is August 21 to include Iso week 31-35

[v-janeyg-msft · Accepted solution]

Hi, @tsak 

After my test, I create a calculated column to diaplay the monthnum you want.

Like this:

monthnum = 
MAXX (
    FILTER (
        'Table',
        [Date] <= EARLIER ( 'Table'[Date] )
            && IF ( WEEKDAY ( [Date], 2 ) = 1, ( MONTH ( [Date] ) ) )
                <> BLANK ()
    ),
    IF ( WEEKDAY ( [Date], 2 ) = 1, ( MONTH ( [Date] ) ) )
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[tsak]

Thank you very much @v-janeyg-msft 

[Burningsuit]

Hi @tsak 

You're going to need a DateTable in your data model. This is a table that has a sequential list of all dates found in your data and columns that define those dates. A simple DateTable for you could look like this..

Date	MondayMonth	Mmno
30/08/2021	August	8
31/08/2021	August	8
01/09/2021	August	8
02/09/2021	August	8
03/09/2021	August	8
04/09/2021	August	8
05/09/2021	August	8
06/09/2021	September	9
07/09/2021	September	9
08/09/2021	September	9

This could be created in Excel, or more powerfully built in Dax or PowerQuery.

In the Datamodel relate the DateTable[Date] to the date column  in your data.

You can then use MondayMonth on charts and visuals to refer to dates that relate to it. 

In the DateTable sort MondayMonth by Mmno (Sort by Column in data view in Power BI Desktop) so that MondayMonth appears in the correct time order, not alphabetical order.

You can do a lot of things with a DateTable to analyse your data by time periods other than the standard.

Read more here Set and use date tables in Power BI Desktop - Power BI | Microsoft Docs

Hope this helps

Stuart