Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jellyjoe
Helper I
Helper I

Line chart that sums count of values by date.

Hey,

I'm having an issue creating a line chart that shows the progress of achieving certain statuses. First however let me explain the situation.

I have two tables, the first table called ,,Planned" shows when a machine i supposed to achieve certain status

MC No.PlannedArrivalUnpacking
1Planned03.11.202003.11.2020
4Planned03.11.202003.11.2020
5Planned03.11.202003.11.2020
7Planned03.11.202003.11.2020
8Planned03.11.202004.11.2020
9Planned03.11.202004.11.2020
10Planned03.11.202004.11.2020
11Planned04.11.202004.11.2020
14Planned04.11.202004.11.2020
12Planned04.11.202005.11.2020
15Planned04.11.202005.11.2020
16Planned04.11.202005.11.2020
17Planned04.11.202005.11.2020
13Planned04.11.202006.11.2020
18Planned04.11.202006.11.2020
19Planned05.11.202006.11.2020
6Planned05.11.202006.11.2020
20Planned06.11.202006.11.2020
21Planned06.11.202007.11.2020
3Planned06.11.202007.11.2020
2Planned07.11.202007.11.2020

So for example, on 03.11.2020 machines 1,4,5,7,8,9,10 are supposed to achieve status ,,Arrival", and machines 1,4,5,7 are supposed to achieve status ,,unpacking"

Second table is the ,,actual' table which shows what statuses were achieved and when. If a record is blank then that means this status havent been achieved by the machine yet

MC No.PlannedArrivalUnpacking
1Actual03.11.202004.11.2020
4Actual 04.11.2020
5Actual03.11.202004.11.2020
7Actual03.11.202004.11.2020
8Actual03.11.2020 
9Actual03.11.2020 
10Actual03.11.2020 
11Actual 05.11.2020
14Actual04.11.202005.11.2020
12Actual04.11.202005.11.2020
15Actual04.11.202005.11.2020
16Actual  
17Actual04.11.202005.11.2020
13Actual04.11.202005.11.2020
18Actual  
19Actual05.11.202006.11.2020
6Actual05.11.202006.11.2020
20Actual06.11.202006.11.2020
21Actual  
3Actual 07.11.2020
2Actual07.11.202007.11.2020

My goal is to make a line chart which would show two lines

a) A line of how many statuses have actually been achieved by a date (not for each day, a sum of how many were achieved by that day)

b) Same line, but showing the plans

Each status would have a separate line chart.

For example, the ,,Arrival" chart would look something like this: 

8e1a32243bc5072bb111a754fe9b1d3c.png

Would really appreciate some help in how i can achieve that.

 

Thanks in advance

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jellyjoe , Not very clear. But someting like this calculate(sum(Table[MC No]), filter(Allselected(Table), Table[Arrival] <= max(Table[Arrival]) && Table[Planned] =max(Table[Planned])))

and actual/planned as measure or legend

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
jellyjoe
Helper I
Helper I

Works like a charm, thank you very much!

amitchandak
Super User
Super User

@jellyjoe , Not very clear. But someting like this calculate(sum(Table[MC No]), filter(Allselected(Table), Table[Arrival] <= max(Table[Arrival]) && Table[Planned] =max(Table[Planned])))

and actual/planned as measure or legend

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.