cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 🙂

8 REPLIES 8
Microsoft
Microsoft

@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

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

@SophieBoucher

 

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

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


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.

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

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors