Forum Discussion

julesdude's avatar
julesdude
Icon for Post Partisan rankPost Partisan
3 years ago

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

6401/01/2020

aaa

60 
aaa6101/07/2023
bbb62 
bbb6301/10/2021
ccc6406/04/2022
ddd6122/03/2023
eee62 
fff6310/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's avatar
    ERD
    Icon for Community Champion rankCommunity Champion

    julesdude , you need to have a virtual table containing these 1s and 0s, and then count them (SUMX).

    • julesdude's avatar
      julesdude
      Icon for Post Partisan rankPost Partisan

      Hi ERD 

      Thanks. How would I be able to achieve this? Would I need to use SUMMARIZE? 
      Any example would help greatly.

      • ERD's avatar
        ERD
        Icon for Community Champion rankCommunity 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] )

  • 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.