Forum Discussion

sarochch's avatar
sarochch
Frequent Visitor
7 years ago
Solved

How to count same ID current month and next month

 

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

 

 

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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