Forum Discussion

Foolke's avatar
Foolke
Icon for Helper I rankHelper I
6 years ago
Solved

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 to...
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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-trend/ba-p/882970

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

  • Foolke's avatar
    Foolke
    Icon for Helper I rankHelper I
    6 years ago

    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 

     

     

    Scr1