cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Foolke
Helper I
Helper I

Calculate totals per date

Hello,

I have 2 tables (see example)

First table contains tickets and a creation date.

Second table contains tickets and closed date.

 

I want to calculate the total created tickets minus the total closed tickets per date. This results need to be visulized in a graph.  

How can I do this?

Thx

 

Created  Created  Total 
2020-01Tic-1 2020-01Tic-7 2020-011
2020-01Tic-2 2020-02Tic-8 2020-02-1
2020-02Tic-3 2020-02Tic-9 2020-031
2020-03Tic-4 2020-03Tic-10 2020-04-2
2020-03Tic-5 2020-04Tic-11   
2020-03Tic-6 2020-04Tic-12   

 

 

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi, @Foolke 

 

take a look at this sample report I created based on your sample data, is it something like this you are after?

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

View solution in original post

7 REPLIES 7
v-diye-msft
Community Support
Community Support

Hi @Foolke 

 

That would be helpful if you can provide the dummy pbix and the expected results in it. 

you might refer to the similar posts here:

https://community.powerbi.com/t5/Desktop/Cumulative-count-based-on-open-and-closed-date/m-p/724776#M... 

https://community.powerbi.com/t5/Desktop/Count-of-Open-cases-over-time/m-p/761764 

https://community.powerbi.com/t5/Desktop/Customer-Service-Dashboard-Created-Tickets-Closed-Tickets-O... 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
amitchandak
Super User
Super User

You can create a common time dimension and join it both dates. this not from your data. But check how to join two table with same date dim: https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

In case they are inside same table, refer:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

@amitchandak Thx for the reply. Unfortunately, the solution doesn't work. What am I doing wrong?

I created a date table, made relations. But when adding the visual it doesn't show the months on the X axis. (see scr1).

@v-diye-msft : goal uis to make the visual as in screenshot, but with the monyths displayed. The line in this 'line clusterd column graph" should give the difference between created and closed tickets.

Link to sample pbx 

 

 

Scr1Scr1

Dates have timestamp. And there where null rows. I created date columns and join

check now https://www.dropbox.com/s/xtc660479i76mae/Monthly%20INC%20report.pbix?dl=0



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!
sturlaws
Resident Rockstar
Resident Rockstar

Hi, @Foolke 

 

take a look at this sample report I created based on your sample data, is it something like this you are after?

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Thx @sturlaws , for some reason didn't see your answer.

Your sample report is excactly what I need.

Unfortunatly ... it doesn't work for me.

Check my example pbx 

sturlaws
Resident Rockstar
Resident Rockstar

If you look at you creation date and closed date, there is a time part, e.g. 01.10.2019 22:31:29. The values in the date table are only dates, or they are like this 01.10.2019 00:00:00, so you won't get a match between the two tables.

 

You can split these creation/closed date into two columns using power query, one with date, the other with time.

 

Or you can create a calculated column where you remove the timepart of the creation date:
new column = left(creation date;9)

 

Just remember to change the relationships to use the new column

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors