problems with calculated column

[Filippo91d]

hi,

I apologize for my bad English.

I have 3 tables. In the first table there is a list of dates "_calendar", in the second "Tickets_DataApertura" and the third "Tickets_Datachiusura" I have a list of tickets with the creation date and the closing date. The ticket tables are related to the calendar table

The fields "TKT_CreationDate - Date", "TKT_ClosureDate - Date" and "Date" are of type date.

I created a slider filter with the date of the "_calendar" table and in the "Tickets_DataApertura" table (green) I filter the open tickets while in the "Tickets_Datachiusura" table (yellow) I filter the closed tickets, based on the dates selected in the filter. Then I have a third measure (red) that calculates how many tickets have been both opened and closed in the range of dates selected

the measure is calculated according to the following formula

Now, I need to calculate in the "Tickets_DataApertura" table (green) a boolean calculated column that tells me if the closing date of the ticket is included or not in the 2 dates of the slider filter of the selected interval.

I have tried everything, but I can't find the desired result. I am writing to ask for help for a possible solution. can you help me please? 🙂

[Anonymous]

Hi @Filippo91d,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.

[Anonymous]

Hi @Filippo91d,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

[Anonymous]

Hi @Filippo91d,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[Filippo91d]

thanks for the suggestion.

can i ask you for help in building the measure that takes into account the date filter? I can't figure out which is the right operation to do that takes all the elements into account.

thanks 🙂

[Deku]

Firstly it looks like you still have auto datetime enabled. You want to right click on the calandar table and mark it as a date table.

Are both of these table identical, but one is joined to the calandar table with creation date and the other with closing date?

[Filippo91d]

ok, I corrected the type of the "date" field of the "_calendar" table with the type you indicated. Thanks!

exactly, they are both of these tables identical. the first one is linked to the "Date" field of the "_calendar" table with the "TKT_CreationDate - Date" field, while the second table is linked to the "Date" field of the "_calendar" table with the "TKT_ClosureDate - Date" field. I need this to find the number of tickets that have the creation date and the closing date within the same date filter. Thanks!

[Deku]

mark date table like this

Since you have the table dedicated to closed dates and that is filtered by date you can just count the number of rows

closed = countrows(Tickets_Datachiusura[TKT_ClosureDate - Date])

You could do this with a singe table if you used the date table as a role playing dimension, something to think about

[Filippo91d]

how can I build the measure I need with the operation you indicated?

I only need a measure that can set a flag to 1 if the tkt has the closing date within the range of dates selected in the date filter, otherwise set it to 0. obviously, in the table, only the tkts that also have the creation date within the range of dates selected must be displayed. 
Example - date selected 2024-12-09 / 2024-12-15

Thanks!

[Anonymous]

Hi @Filippo91d,

Thank you for reaching out in Microsoft Community Forum.

Thank you @Deku  for the helpful response.

Please use below DAX for the required output

1.Create a measure for the flag

TKT_ClosedInRange_Flag =
VAR StartDate = MIN('_calendar'[Date])
VAR EndDate = MAX('_calendar'[Date])
RETURN
IF (
MAX('Tickets_DataApertura'[TKT_ClosureDate - Date]) >= StartDate &&
MAX('Tickets_DataApertura'[TKT_ClosureDate - Date]) <= EndDate,
1,
0
)

2.Apply a visual-level filter

TKT_OpenInRange_Flag =
VAR StartDate = MIN('_calendar'[Date])
VAR EndDate = MAX('_calendar'[Date])
RETURN
IF (
MAX('Tickets_DataApertura'[TKT_CreationDate - Date]) >= StartDate &&
MAX('Tickets_DataApertura'[TKT_CreationDate - Date]) <= EndDate,
1,
0
)

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[Deku]

Calculated columns are calculated during a model refresh and will therefore not respect filter/slicers on a report page. You need to use a measure