cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SophieBoucher Frequent Visitor
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,

calcul_nombre.xlsx - Excel.jpg

 

Thank you very match Smiley Happy

8 REPLIES 8
Moderator Eric_Zhang
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.

Capture.PNG

 

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

Capture.PNG

SophieBoucher Frequent Visitor
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 Eric_Zhang
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. Smiley Happy

SophieBoucher Frequent Visitor
Frequent Visitor

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

Projet_Mantis - Power BI Desktop_2.jpg

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

SophieBoucher Frequent Visitor
Frequent Visitor

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

thank you @Eric_Zhang

Moderator 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. Smiley Happy

Highlighted
SophieBoucher Frequent Visitor
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?

 

 

 

PrtScr capture_2.jpg