Help with generating a summary table

[AAOLIVARESR]

Hello,

I want to create a summary table in Power BI that tells me the amount of tickets for a week for example:

From	to
2024-20-05	2024-26-05
Tickets in	Tickets out
8	10

From the following example dataset:

Ticket	Date in	Date out
A-1	12-04-2024	20-05-2024
A-2	19-04-2024	20-05-2024
A-3	21-05-2024	20-05-2024
A-4	21-05-2024	21-05-2024
A-5	21-05-2024	22-05-2024
A-6	21-05-2024	23-05-2024
A-7	25-05-2024	24-05-2024
A-8	25-05-2024	25-05-2024
A-9	25-05-2024	25-05-2024
A-10	25-05-2024	25-05-2024

The problem I have is that I can't find a way to assign the number of tickets in the same table or chart using different dates (see example) because tickets that are closed during the week were not necessarily created that same week.

[Anonymous · Accepted solution]

Hi,@AAOLIVARESR. Hello,@ryan_mayu ,thanks for your concern about this issue.And I would like to share some additional solutions below.
I am glad to help you.

According to your description, you want to count the number of tickets in a selected time period

You could refer to my test result below, hope it can help you.
Here is my test data:

I created two measures to calculate the number of Tickets whose DateIn and DateOut are within the range selected by the slicer.

Here is the Dax code

M_ticketIn = 
VAR minDate=MIN('Dates'[Date])
VAR maxDate=MAX('Dates'[Date])
RETURN
CALCULATE(
    COUNT(SaleTicket[Ticket]),FILTER(ALL(SaleTicket),'SaleTicket'[Date in]>=minDate&&'SaleTicket'[Date in]<=maxDate))

M_ticketOut = 
VAR minDate=MIN('Dates'[Date])
VAR maxDate=MAX('Dates'[Date])
RETURN
CALCULATE(
    COUNT('SaleTicket'[Ticket]),FILTER(ALL('SaleTicket'),'SaleTicket'[Date out]>=minDate&&'SaleTicket'[Date out]<=maxDate))

I've created a measrue M_result that counts the number of Tickets that match both DateIn and DateOut for the selected time period.

i.e. the number of tickects that start and end in the selected time period.

M_result = 
IF('SaleTicket'[M_ticketIn]<='SaleTicket'[M_ticketOut],'SaleTicket'[M_ticketIn],'SaleTicket'[M_ticketOut])

You can also try to use the WeekNum function to get the number of weeks for each date to calculate (this avoids having to set the time period manually).
Here is the test result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

I cannot understand your expected result of 8 and 10?  Explain that in detail.

[Anonymous · Accepted solution]

Hi,@AAOLIVARESR. Hello,@ryan_mayu ,thanks for your concern about this issue.And I would like to share some additional solutions below.
I am glad to help you.

According to your description, you want to count the number of tickets in a selected time period

You could refer to my test result below, hope it can help you.
Here is my test data:

I created two measures to calculate the number of Tickets whose DateIn and DateOut are within the range selected by the slicer.

Here is the Dax code

M_ticketIn = 
VAR minDate=MIN('Dates'[Date])
VAR maxDate=MAX('Dates'[Date])
RETURN
CALCULATE(
    COUNT(SaleTicket[Ticket]),FILTER(ALL(SaleTicket),'SaleTicket'[Date in]>=minDate&&'SaleTicket'[Date in]<=maxDate))

M_ticketOut = 
VAR minDate=MIN('Dates'[Date])
VAR maxDate=MAX('Dates'[Date])
RETURN
CALCULATE(
    COUNT('SaleTicket'[Ticket]),FILTER(ALL('SaleTicket'),'SaleTicket'[Date out]>=minDate&&'SaleTicket'[Date out]<=maxDate))

I've created a measrue M_result that counts the number of Tickets that match both DateIn and DateOut for the selected time period.

i.e. the number of tickects that start and end in the selected time period.

M_result = 
IF('SaleTicket'[M_ticketIn]<='SaleTicket'[M_ticketOut],'SaleTicket'[M_ticketIn],'SaleTicket'[M_ticketOut])

You can also try to use the WeekNum function to get the number of weeks for each date to calculate (this avoids having to set the time period manually).
Here is the test result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

why the number for tickets in is 8?