Create a DateTable with columns for Date, Count of Tickets Created and Count of Tickets Closed

[Emmerson]

I want to create a table visualisation with dates as headings and count of Tickets Created vs Closed in rows. The relevant columns in my 'All tickets - all accounts' table are Ticket ID, Month Created and Month Closed. All Ticket IDs have a Month Created but only some have a Month Closed. The problem I have is: if I use Month Created as columns, my count for tickets closed in that month ignores all tickets created in previous months. How do overcome this issue so that my table includes all tickets created vs closed in that month (including tickets created in previous months)?

My thinking is that I need to create a custom DateTable with columns for Date, Count of Tickets Created and Count of Tickets Closed. I have created that custom DateTable with a date range between 1 Jan 2020 and today's date. Now I want to add those 2 columns for 'count of tickets created' and 'count of tickets closed' by date. I am running into problems (as shown below). Does anyone have a solution? 

[TomMartens · Accepted solution]

Hey @Emmerson ,

I recommend reading this article: https://www.daxpatterns.com/time-patterns/

The article contains almost anything you need to know about date-related calculations with DAX.

Indeed, it is recommended to create a separate date-table or Calendar table, but then it's also recommended to create a relationship between your existing table and the new Calendar table creating a data model following the concept of dimensional modeling: https://learn.microsoft.com/en-gb/training/paths/model-power-bi/

Instead of creating a table, I recommen creating a DAX measure like so:

SalesAmount cumulated All time = 
var maxdatekey = CALCULATE( MAX( 'DimDate'[Datekey] ) )
return

CALCULATE( SUM( FactOnlineSales[SalesAmount] ) , 'DimDate'[Datekey] <= maxdatekey )

Then you can create simple table visual:

Be aware that the column CalendarMonth is coming from my CalendarTable.

Hopefully, this helps to tackle your challenge.

Tom