Forum Discussion
Bar Chart Measure Needed to Count Total Dates Beyond a Set Date
Hi,
I'm having some difficulty getting some DAX to work in favour of a bar chart I have in my report.
Here is a watered down version of the table in my data model that the bar chart references:
| asset lease unit reference | Notice Period | Option Effective Date ID | New Option Effective Date |
aaa | 6 | 4 | 01/01/2020 |
aaa | 6 | 0 | |
| aaa | 6 | 1 | 01/07/2023 |
| bbb | 6 | 2 | |
| bbb | 6 | 3 | 01/10/2021 |
| ccc | 6 | 4 | 06/04/2022 |
| ddd | 6 | 1 | 22/03/2023 |
| eee | 6 | 2 | |
| fff | 6 | 3 | 10/12/2022 |
In my report I have a date slicer (the "from" date is hidden but the "to" date is selectable). This enables the user to pick a date. The measure [as of date] is assigned to this.
That date is then used as the start date from which the bar chart begins displaying dates on its x-axis.
I want the y-axis to be a count of each date and the chart to have a drill down ability so that you can drill down from years to quarters, to months and then days and the count will be summed or split accordingly.
When qualifying a date should be counted, there are some rules that need to be applied. Sometimes there is no date in "New Option Effective Date". If this is the case, if the "Option Effective Date ID" is 0 then nothing should be counted. However, if the "Option Effective Date ID" is 2 then the [as of date] should be used advancing it in months using the number in the "Notice Period" column.
I have a fully functioning DateTable in my data model. I am not sure whether to link to it.
Unfortunately the chart that I am currently getting looks like the below - dates seem to be identified but the count is not accumulating for each month or whatever level is drilled down to. And dates showing FROM the as of date is not working as it goes back to 2016:
My DateTable is placed in the x-axis.
My DAX measure for the Y-axis is:
Option Effective Count Dates =
VAR asofdate = [As Of Date]
VAR BaseDate =
IF(
ISBLANK(MAX(Option_DST[New Option Effective Date])) && MAX(Option_DST[Option Effective Date ID]) = 2,
EDATE(asofdate, MAX(Option_DST[Notice Period])),
MAX(Option_DST[New Option Effective Date])
)
RETURN
IF(
NOT ISBLANK(BaseDate) && BaseDate >= asofdate,
1,
BLANK()
)
Where am I going wrong? Do I need a SUMX on the IF statement at the end?
11 Replies
- ERD
Community Champion
julesdude , I'm not sure how to achieve this without a second date table for the slicer here. But you can try this:
Measure = VAR dt = MAX( 'Date_2'[Date] ) VAR t = ADDCOLUMNS ( ADDCOLUMNS ( 'Table', "newdt", VAR noed = CALCULATE ( MAX ( 'Table'[New Option Effective Date ] ) ) VAR oedid = CALCULATE ( MAX ( 'Table'[Option Effective Date ID ] ) ) VAR np = CALCULATE ( MAX ( 'Table'[Notice Period ] ) ) VAR basedate = IF ( ISBLANK ( noed ) && oedid = 2, EDATE ( dt, np ), noed ) RETURN basedate ), "check", IF ( NOT ISBLANK ( [newdt] ) && [newdt] >= dt, 1, BLANK () ), "newdt_end", EOMONTH ( [newdt], 0 ) ) VAR c_dt = MAX( 'Date'[Date] ) RETURN SUMX( FILTER ( t, IF ( ISINSCOPE('Date'[Date] ), [newdt] = c_dt, [newdt_end] = c_dt ) ), [check] )
- technolog
Super User
I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.
Your insights and updates will greatly assist others who might be encountering the same challenge.