Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I have a transaction table that has a start date and completion date columns. I need to determine the count of OPEN transactions per month. A transaction is considered open for a reference month if its start date is on or before that month, AND ( Completion date is either blank, OR its month is after the reference month). I have to have it in such a way, so when I plot its graph, report filters using the transaction table columns are still applicable.
For example: the first table shows the data set, and the second table shows the expected Open ItemRunning Balance.
The challenge I have is to come up with the correct measure so that it is report filterable on the fly.
Thank you in advance.
Solved! Go to Solution.
// For this you need a DateTable that's DISconnected from Documents.
// You want this measure to work correcly not only for months
// but for any period of time, even 1 day. Generally, you should
// always aim at creating measures that make sense for ANY
// selection in dimensions. I'd also suggest that you not use
// BLANKs for Completion Date. Instead, use something like
// DATE(3000, 3, 1). This will make the code sipler and potentially
// faster as you won't need to check for BLANK dates. Of course,
// the Start Date and Completion Date columns should be hidden.
[# Open Docs] =
var __lastVisibleDate = MAX( DateTable[Date] )
var __result =
CALCULATE(
COUNTROWS( Documents ),
// assuming that Start Date will never be BLANK...
Documents[Start Date] <= __lastVisibleDate,
OR(
Documents[Completion Date] > __lastVisibleDate,
// If you remove BLANKS as suggested, you can
// remove OR and leave only the first condition
// under OR.
ISBLANK( Documents[Completion Date] )
)
)
RETURN
__result
// For this you need a DateTable that's DISconnected from Documents.
// You want this measure to work correcly not only for months
// but for any period of time, even 1 day. Generally, you should
// always aim at creating measures that make sense for ANY
// selection in dimensions. I'd also suggest that you not use
// BLANKs for Completion Date. Instead, use something like
// DATE(3000, 3, 1). This will make the code sipler and potentially
// faster as you won't need to check for BLANK dates. Of course,
// the Start Date and Completion Date columns should be hidden.
[# Open Docs] =
var __lastVisibleDate = MAX( DateTable[Date] )
var __result =
CALCULATE(
COUNTROWS( Documents ),
// assuming that Start Date will never be BLANK...
Documents[Start Date] <= __lastVisibleDate,
OR(
Documents[Completion Date] > __lastVisibleDate,
// If you remove BLANKS as suggested, you can
// remove OR and leave only the first condition
// under OR.
ISBLANK( Documents[Completion Date] )
)
)
RETURN
__result
Thanks Daxer! I have accepted it as the solution.
use a CROSSFILTER(...,NONE) to fill in the date gaps. That will then allow your measure to mark each transactions status by date.
Hi Ibendlin, please elaborate on the application of crossfilter. Thank you
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |