cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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

1 ACCEPTED SOLUTION
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors