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
Emmerson
New Member

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

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? 

 

Emmerson_0-1688099715984.png

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:
image.png

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

 

Hopefully, this helps to tackle your challenge.

 

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

1 REPLY 1
TomMartens
Super User
Super User

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:
image.png

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

 

Hopefully, this helps to tackle your challenge.

 

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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