calculate the number of project according 2 dates DAX formulas

[SophieBoucher]

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 🙂

[Eric_Zhang]

@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])

[SophieBoucher]

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

[Eric_Zhang]

@SophieBoucher

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

[SophieBoucher]

@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

[Eric_Zhang]

---

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.

[SophieBoucher]

thank you @Eric_Zhang

[Eric_Zhang]

@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. 🙂

[SophieBoucher]

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?