The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have a project table which have Start date end date project name and i have date table that is generating calender date, i need a line chart that will show ongoing project month wise, You cant create calculated field or any extra tables
Project Name | Start Date | End date |
elivie | 1/11/2021 | 17/02/2022 |
abc | 7/11/2021 | 8/3/2022 |
mydata | 12/12/2021 | 7/8/2022 |
helloworld | 11/12/2022 | 6/5/2022 |
Output
ongoingproject | |
Nov-21 | 2 |
Dec-22 | 2 |
Jan-22 | 4 |
Feb-22 | 4 |
Mar-22 | 3 |
Apr-22 | 2 |
May-22 | 2 |
Jun-22 | 1 |
Solved! Go to Solution.
Hi @Anonymous,
Calculated column is a necessity for you need a MMM-YY column to create your visual but it won’t generate in your model automatically.
OngoingMonth = FORMAT('calendar'[Date],"mmm-yy")
After creating this Calculated column, a Measure can achieve your needs.
OngoingProject =
VAR firstDay =
FIRSTDATE ( 'calendar'[Date] )
VAR lastDay =
LASTDATE ( 'calendar'[Date] )
RETURN
CALCULATE (
COUNT ( 'Table'[Project Name] ),
FILTER (
ALL ( 'Table' ),
'Table'[Start Date] <= lastDay
&& 'Table'[End date] >= firstDay
)
)
Sample data and the result look like this.
Also, attached the pbix file.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi @Anonymous,
Calculated column is a necessity for you need a MMM-YY column to create your visual but it won’t generate in your model automatically.
OngoingMonth = FORMAT('calendar'[Date],"mmm-yy")
After creating this Calculated column, a Measure can achieve your needs.
OngoingProject =
VAR firstDay =
FIRSTDATE ( 'calendar'[Date] )
VAR lastDay =
LASTDATE ( 'calendar'[Date] )
RETURN
CALCULATE (
COUNT ( 'Table'[Project Name] ),
FILTER (
ALL ( 'Table' ),
'Table'[Start Date] <= lastDay
&& 'Table'[End date] >= firstDay
)
)
Sample data and the result look like this.
Also, attached the pbix file.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
@Anonymous
User | Count |
---|---|
26 | |
10 | |
8 | |
5 | |
5 |
User | Count |
---|---|
33 | |
13 | |
11 | |
9 | |
8 |