How to group my YearMonth field into just 3 values?

[ansa_naz]

Hi all

I have the following two tables - dates are in UK dd/mm/yyyy format:

Datetable - contains all dates from 01/01/2000 to 31/12/2020, along with a YearMonth column:

Date                  YearMonth

01/01/2018       2018-01

02/01/2018       2018-01

03/01/2018       2018-01

...

01/02/2018       2018-02

01/03/2018       2018-03

01/04/2018       2018-04

01/05/2018       2018-05

01/06/2018       2018-06

...etc

Infotable

ID                     Date

1                      01/01/2018

2                      05/01/2018

3                      07/02/2018

4                      09/03/2018

5                      01/05/2018

6                      12/05/2018

7                      18/06/2018

The Datetable is joined to the Infotable on the Date columns

I want to return the following count of items from the Infotable:

YearMonth                                                  Count

HighestYearMonthFromInfotable                 X

SecondHighestYearMonthFromInfotable      X

<SecondHighestYearMonthFromInfotable    X

So for the above example data from Infotable, I would get:

YearMonth            Count

2018-06                1

2018-05                2

<2018-05             4

Bearing in mind that the YearMonth column in this count will always be changing, as the Infotable is always changing, any idea how I could do this? The data returned also needs to be used with other filters on the report page

Many thanks for all help

Naz

[ansa_naz · Accepted solution]

So my final solution was to use a calculated column, using bits of DAX from the measure provided by Dale and bit of the DAX provided by Ryan:

YearMonthGroup = 
VAR maxYearMonth =
    FORMAT ( MAX ( Infotable[LogDate] ), "yyyy-mm" )
VAR secondYearMonth =
    FORMAT (
        CALCULATE (
            MAX ( Infotable[LogDate] ),
            FILTER (
                'Infotable',
                'Infotable'[LogDate] <= EOMONTH ( MAX ( Infotable[LogDate] ), -1 )
            )
        ),
        "yyyy-mm"
    )
return
    IF (maxYearMonth=FORMAT(Infotable[LogDate],"yyyy-mm"),maxYearMonth,
    IF(secondYearMonth=FORMAT(Infotable[LogDate],"yyyy-mm"),secondYearMonth,"<"&secondYearMonth))

Hope that helps someone!

[ryan_mayu]

@ansa_naz

For me, I will create a new group column.

column1 =

VAR MaxYear=YEAR(MAX('Sheet5'[Date]))

VAR MaxMonth= MONTH(MAX('Sheet5'[Date]))

VAR test= if(MONTH('Sheet5'[Date])=MaxMonth&&YEAR('Sheet5'[Date])=MaxYear,"Highest Month",

if(MaxMonth=1&&MONTH('Sheet5'[Date])=12&&YEAR('Sheet5'[Date])=MaxYear-1,"Second Highest",

if(MONTH('Sheet5'[Date])=MaxMonth-1&&YEAR('Sheet5'[Date])=MaxYear,"Second Highest","Others")))

return test

I added some data for Jan 2019 and Dec 2018 to test. The result in column1 will change automatically and looks correct as well.

Thanks and BR

Ryan

[ansa_naz]

Hi @ryan_mayu thanks for your reply. I used your suggestion of creating a new calculated column, but just tweaked your formula to include the actual Year-Month values instead of 'Highest Month' etc. Cheers!

[v-jiascu-msft]

Hi Naz,

You can group them by right click the column (or click). Please refer to the snapshot below. Finally, you can use the new groups in the visual.

Best Regards,
Dale

[ansa_naz]

Hi @v-jiascu-msft thanks for your reply. I have reworded my question as it wasnt very clear. As you can see, the YearMonth list is dynamic, it varies because the Infotable is always changing. Is there any way to make these groups dynamic too?

[v-jiascu-msft]

Hi @ansa_naz,

The context can't be dynamic for now. Please download the solution from the attachment. 

1. Create a new table. 

Rank                   Value

Highest	1
SecondHighest	2
Others	3

2. Create two measures.

YearMonth Measure =
VAR maxYearMonth =
    FORMAT ( MAX ( Infotable[LogDate] ), "yyyy-mm" )
VAR secondYearMonth =
    FORMAT (
        CALCULATE (
            MAX ( Infotable[LogDate] ),
            FILTER (
                'Infotable',
                'Infotable'[LogDate] <= EOMONTH ( MAX ( Infotable[LogDate] ), -1 )
            )
        ),
        "yyyy-mm"
    )
RETURN
    IF (
        MIN ( 'Table1'[Value] ) = 1,
        maxYearMonth,
        IF ( MIN ( Table1[Value] ) = 2, secondYearMonth, "<" & secondYearMonth )
    )

CountAmount Measure =
VAR maxYearMonth =
    MAX ( Infotable[LogDate] )
VAR secondYearMonth =
    CALCULATE (
        MAX ( Infotable[LogDate] ),
        FILTER (
            'Infotable',
            'Infotable'[LogDate] <= EOMONTH ( MAX ( Infotable[LogDate] ), -1 )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Table1[Rank] ),
        IF (
            MIN ( 'Table1'[Value] ) = 1,
            CALCULATE (
                COUNT ( Infotable[ID] ),
                DATESINPERIOD ( DateTable[Date], EOMONTH ( maxYearMonth, 0 ), -1, MONTH )
            ),
            IF (
                MIN ( Table1[Value] ) = 2,
                CALCULATE (
                    COUNT ( Infotable[ID] ),
                    DATESINPERIOD ( DateTable[Date], EOMONTH ( secondYearMonth, 0 ), -1, MONTH )
                ),
                CALCULATE (
                    COUNT ( Infotable[ID] ),
                    'DateTable'[Date] <= EOMONTH ( secondYearMonth, -1 )
                )
            )
        ),
        COUNT ( Infotable[ID] )
    )

Best Regards,
Dale

[ansa_naz]

Hi @v-jiascu-msft thanks for your reply.

I would like to remove the Rank column in this table you have provided - however, removing this column makes the YearMonth Measure show just one row, instead of the 3 when Rank column is present - any idea how to fix it?

Many thanks

[AlB]

Hi @ansa_naz

I'm not sure I understand what you need but let's try this for your 'Count' column

Datetable[Count] =
SWITCH (
    TRUE ();
    MONTH ( Datetable[Date] ) = 6; 1;
    MONTH ( Datetable[Date] ) = 5; 2;
    MONTH ( Datetable[Date] ) < 5; 4
)

[ansa_naz]

Hi @AlB I have reworded my question, hopefully it makes more sense now! Thank you for your provided answer, but I dont think it will work per my reworded question? As the YearMonth on the count is a dynamic column, the highest YearMonth could be 2018-07, it could be 2018-02, etc. The Infotable is always changing