Forum Discussion
Date filtering
Hi.
This might be very simple but somehow I dont know what to do here.
I have my calender table and then I have a start date and an end date in my data table.
If I filter on a period between 1/10/2022 and 1/31/2022 where it says there was 43 open incidents in that period but that is incorrect or it is not what I would like to visualise. This only shows me how many was registered(start date) in october but I would like to know how many open incidents there were in october including the incidents that were registered before 1/10/2022. So it kind of have to sum up all the open incidents until the last date.
Best regards,
Lisa
4 Replies
Hi Anonymous ,
if you need to create measures regarding open incidents, first you have to create a new calculated table named "Open Incident". You can use Power Query or DAX.
For example in DAX the functions to be used are GENERATE and DATESBETWEEN.
Open Incident =
GENERATE(
'Table',
DATESBETWEEN(
'Date'[Date],
'Table'[StartDate],
'Table'[EndDate]
)
)
Then you get the new column "Date" in your table and you can create the mesure DISTINCTCOUNT('Table'[ID Incident]) in order to count the open incidents.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
