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
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?
Solved! Go to 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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |