How to count same ID current month and next month

[sarochch]

Hi all,

I have a table and want to create summary table like example below. 

example: (Column G)

Report Jan-18 : count all ID (Jan18) in Feb-18 which no. of day >30

Report Feb-18: count all ID (Feb18) in Mar-18 which no. of day >30

Each month no duplicate ID.

How to use DAX or another way to solve it ?

Thank you

[v-yulgu-msft · Accepted solution]

Hi @sarochch,

Suppose the source data table is called 'Tab_3', please refer to below DAX to generate a calculated table:

Tab_4 =
SUMMARIZE (
    Tab_3,
    Tab_3[Date],
    "No. of day >=8", CALCULATE ( COUNT ( Tab_3[ID] ), Tab_3[No.of day] >= 8 ),
    "No. of day >30 next month", COUNTROWS (
        INTERSECT (
            VALUES ( Tab_3[ID] ),
            CALCULATETABLE (
                VALUES ( Tab_3[ID] ),
                FILTER (
                    ALL ( Tab_3 ),
                    Tab_3[No.of day] > 30
                        && YEAR ( Tab_3[Date] ) = YEAR ( EARLIER ( Tab_3[Date] ) )
                        && MONTH ( Tab_3[Date] )
                            = MONTH ( EARLIER ( Tab_3[Date] ) ) + 1
                )
            )
        )
    )
)

Best regards,

Yuliana Gu