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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
vincenardo
Helper I
Helper I

Create line chart that counts dates from two columns in data set and displays count by date

In my data set, I have two columns with dates. DteOnHold & DteOffHold

 

vincenardo_0-1631794879557.png

I want to create a line chart that counts the the number of dates for each column (DteOnHold & DateOffHold) by day.

I created a Stacked Area Chart and placed the DteOnHold and DteOffHold in the AXIS and Count of DteOnHold & Count DteOffHold in the Values - but this is not giving the correct results in the line chart.

 

Any suggestions?

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @vincenardo ;

You could create two measures as follows:

count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))
count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))

In addition , two tables don't have relationship.

The final output is shown below:

vyalanwumsft_0-1632126741272.png

If not right ,can you share the output and more details  you want to achieve?


Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

Hi, @vincenardo ;

You could create two measures as follows:

count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))
count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALL('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))

In addition , two tables don't have relationship.

The final output is shown below:

vyalanwumsft_0-1632126741272.png

If not right ,can you share the output and more details  you want to achieve?


Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

For additional filtering, how can I modify the COUNT formulas so that I can use a slicer to filter the chart based on the Task Name?

Hi, @vincenardo ;

If you have slicer, you could modify it.

count of DteOnHold = CALCULATE(COUNT([DteOnHold]),FILTER(ALLSELECTED('Table'),[DteOnHold]=MAX('Calendar Relative'[Date])))

change ALL() to ALLSELECTED().

Best Regards,
Community Support Team_ Yalan Wu

This works perfectly! Thanks!

 

amitchandak
Super User
Super User

@vincenardo , You can create a common date table, Join both dates with date of date table . One join will be inactive, that can be activated using userelationship

 

refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks @amitchandak -- I need some additional help, I am still a bit confused.

I created a date table using this formula -

Calendar Relative = CALENDAR(
TODAY()-365,
TODAY()+10
 
Linked the DteOffHold to the Date in 'Calendar Relative' table (Made it active) and linked the DteOnHold to the same table.
 
vincenardo_0-1631802606633.png

I am confused on what calculations I need to create next.

Thanks!

 

 

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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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