cancel
Showing results for
Did you mean:
Frequent Visitor

## calculate the number of project according 2 dates DAX formulas

I wanted to show the number of open projects and the number of projects closed on a time axis as shown on the display next (this is not true), ie on a time axis if I select 2016 I must have a graph showing me in February 2016: I have 5 open and 0 closed so on for every month.

Here is a excel example to explain what I want exactly.
I have reduced the field to make simple, in short there is an opening date and closing date for each project and according to the dates of opening and closing,

Thank you very match

8 REPLIES 8
Moderator

## Re: calculate the number of project according 2 dates DAX formulas

@SophieBoucher

The snapshot is blurred so I can't see what exactly shown. According to your description, I think you may need a sample as below.

Supposing the sample data is as given above. Create two calculate columns.

```FirstDay of Start Date = DATE(YEAR(OriginalTable[Start Date]),MONTH(OriginalTable[Start Date]),1)

FirstDay of End Date = DATE(YEAR(OriginalTable[End Date]),MONTH(OriginalTable[End Date]),1)```

Then create a calender table.

`CalculatedTable = FILTER(CROSSJOIN(OriginalTable,DimDate),DimDate[Date]>=OriginalTable[FirstDay of Start Date]&&DimDate[Date]<=OriginalTable[FirstDay of End Date])`

Create another calculated table.

`CalculatedTable = FILTER(CROSSJOIN(OriginalTable,DimDate),DimDate[Date]>=OriginalTable[FirstDay of Start Date]&&DimDate[Date]<=OriginalTable[FirstDay of End Date])`

Then create two measures

```Project Open = COUNTAX(FILTER('CalculatedTable','CalculatedTable'[Date]<>'CalculatedTable'[FirstDay of End Date]),'CalculatedTable'[Date])

Project Closed = COUNTAX(FILTER('CalculatedTable','CalculatedTable'[Date]='CalculatedTable'[FirstDay of End Date]),'CalculatedTable'[Date])```

Frequent Visitor

## Re: calculate the number of project according 2 dates DAX formulas

Thank you very match Eric, can you explain me why you use first day for the list of Dates, because i like filter there with week also, and i haven't understand any file you used to display the data graphically, because the values do not match my database file.

Thank you very match in advance

Moderator

## Re: calculate the number of project according 2 dates DAX formulas

@SophieBoucher

Could you please post a high-res snapshot? My last approach is just by guessing.

Frequent Visitor

## Re: calculate the number of project according 2 dates DAX formulas

@Eric_Zhang

I have tickets (id) per project (project_id), these tickets have two different dates, when I want to aggregate under these 2 dates 1 submission date (date_soumission) and 2 processing date (date_traitement), the problems is that tickets can be empty (untreated), I wanted to calculate the number of ticket submitted (1) and the number of tickets processed (2) at the same time grid.

AM I explain you very well?

Thanks

Sophie

Moderator

## Re: calculate the number of project according 2 dates DAX formulas

the problems is that tickets can be empty (untreated), I wanted to calculate the number of ticket submitted (1) and the number of tickets processed (2) at the same time grid.

I'm still not clear what you'd like? Do you mean something like

```cnt =
CALCULATE (
COUNTA ( 'table'[id] ),
'table'[date_soumission] <> BLANK ()
&& 'table'[date_traitement] <> BLANK ()
)```

Please post some sample data in text and expected output to clear the confusion.

Frequent Visitor

## Re: calculate the number of project according 2 dates DAX formulas

thank you @Eric_Zhang

Moderator

## Re: calculate the number of project according 2 dates DAX formulas

@SophieBoucher

How is your report going on? If no further questions, please accept any reply(s) making sense as solution to close this thread. For any question, feel free to post.

Highlighted
Frequent Visitor

## Re: calculate the number of project according 2 dates DAX formulas

it is not completely solved, in fact I found a solution to my problem is to count the number opened and closed by creating an UNPIVOT on ID, here I meet a primary key duplication problem, do you have an idea?

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,271 guests
Recent signins: