Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Display months based on days

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

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    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

3 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Anonymous 

     

    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

  • Burningsuit's avatar
    Burningsuit
    Resident Rockstar
    4 years ago

    Hi Anonymous 

    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..

     

    DateMondayMonthMmno
    30/08/2021  August8
    31/08/2021  August8
    01/09/2021  August8
    02/09/2021  August8
    03/09/2021  August8
    04/09/2021  August8
    05/09/2021  August8
    06/09/2021  September9
    07/09/2021  September9
    08/09/2021  September9

     

    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