Forum Discussion
Calculate if value is between dates
Hello all,
I already tried making this topic last week, but it seems to have vanished.
I'm new to power BI and dax and could use some assistance.
I have a table with cases something like the table below:
ID createdate closedate
1 01-01-17 15-02-17
2 02-01-17 10-01-17
3 03-01-17 null
The close date can be "null", meaning that the case is still open. I also created a date dimension table, with everything from year to days, there is no relation between the Date and Case table.
I need an meassurement which will count all the "active" cases. Meaning that it will count all the cases that are open on a certain date. example: on the 9th of Januari (09-01-17) there are 3 cases active. On the 20th of januari (20-01-17) there are 2 cases active (1 and 3 in the example).
Can you help me out? i have been trying different measurements and codes for a few days now!
Thank you very much in advance!
11 Replies
Hi,
Have you tested the result? Does the solution work? In the Query Editor, i have basically created a single column of dates from your start and end date columns. So the query loaded to the Data Model has only 2 columns - ID and Date.
Good to hear.
Hi Anonymous
First thing to do is, have a time slicer to select the date you want to see the information from.
Next thing you do, create meausre:
active = CALCULTATE(COUNTROWS(tablename), closedata = "null")
Then select your date you want to see
hi miltenburger,
Thank you for your answer. Unfortunatly this seems to return an error, and with some changes it only seem to retrieve cases that are currently open (closedate null). I'm looking for all the active cases of a day in history (the slicer was a good idea!).
so it should only return cases for which:
create date <= [date selected] < end date AND create date <= [date selected with end date = null.
Any further ideas? Cause it is greatly appriciated :)!
