cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Counting Submission and Closure Trend by Month

Hi There,

I have a table with the following columns and values:-

ID | SubmissionDate | ClosureDate | OtherColumns

1   | 21-12-2016        | 23-03-2017  |

2   | 12-01-2017        | 01-04-2017  |

Any idea how to create a graph based on the count of submission and closure by different months?

1 ACCEPTED SOLUTION
Employee

Hi @Juliana,

You create a Calendar table including all the dates, please click "New Table" under modeling type the following formula on home page.

`Dates=Calendar(MIN(Table[SubmissionDate]),MAX(ClosureDate))`

Then create a calculated month column using the formula.

`Month=MONTH(Dates[Dates])`

Create a measure using the formula.

```result=Calculate(
count(Table[ID]),
FILTER(Table,
COUNTROWS(FILTER(VALUES(Dates[Dates]),
Project[SubmissionDate] <= Dates[Dates] &&
Project[ClosureDate] >= Dates[Dates] ))
> 0)
)

```

Create a table visual, select the Table[Month] and [result] as value levels.

Best Regards,
Angelia

Employee

Hi @Juliana,

You create a Calendar table including all the dates, please click "New Table" under modeling type the following formula on home page.

`Dates=Calendar(MIN(Table[SubmissionDate]),MAX(ClosureDate))`

Then create a calculated month column using the formula.

`Month=MONTH(Dates[Dates])`

Create a measure using the formula.

```result=Calculate(
count(Table[ID]),
FILTER(Table,
COUNTROWS(FILTER(VALUES(Dates[Dates]),
Project[SubmissionDate] <= Dates[Dates] &&
Project[ClosureDate] >= Dates[Dates] ))
> 0)
)

```

Create a table visual, select the Table[Month] and [result] as value levels.

Best Regards,
Angelia

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors