Reporting different counts on a date column

[LeightonG]

Hi there,

I wonder if anyone can help?

I have a date column in a table called 'Expires' and I want to show a count of any dates that fall into the following ranges:

Any dates that have already passed (expired)

Any dates between today and up to 15 days from today

Any dates between today+16 days up to 30 days from today

Any dates between today+31 days up to 60 days from today

To approach this, I created four new columns in Power BI called:

'Expired'

'Expires in 15 days'

'Expires in 30 days'

'Expires in 60 days'

I dragged the 'Expires' column into each one and set it to 'Count'

Then, I used this formula in the 'Expires in 15 days' column:

Expires in 15 Days =

CALCULATE(

COUNTROWS('Table'),'Table'[Expires] <= TODAY()+15, 'Table'[Expires] >= TODAY(), NOT ISBLANK('Table'[Expires])

)

And it works a treat.

However, if I use that in any of the other columns, I get a circular dependency error.

So, my next approach was to use this formula (in the Expires in 60 days column):

Expires in 60 Days =

If(

ISEMPTY(

FILTER('Table', NOT(ISBLANK([Expires])) && ([Expires]>=TODAY()+31 && [Expires]<=TODAY()+60))),

0,

COUNTROWS(FILTER('Table',NOT(ISBLANK([Expires])) && ([Expires]>=TODAY()+31 && [Expires]<=TODAY()+60)))

)

This just returns all records (even if they are blank).

I can't work out what I'm doing wrong?

Thanks in advance for any assistance.

[Fowmy]

@LeightonG 

What is the Custom Visual that you are using here?

You select the visual, drag and drop the Expiry Bracket field to the Visual filter in the filter pane and select each item for each visual.

[LeightonG]

Thanks.  That is the Tachometer visual (I don't think it's custom?). I have four of them in a row.

I have dragged the Expiry Bracket field, however that is just a blank text field?

[Fowmy]

@LeightonG 

You can set the bracket as follows in the filters pane

[LeightonG]

Thanks.

I think this is too complicated for me!

When I select that it just brings back the value set in the table (15). The same as in your example above. Do you know how I can get that to display the count of all dates in my table where the column 'Expires' fall within the 15 day range?

[Fowmy]

@LeightonG 

My Measure counts the number of dates on Expires column that fall within the 15-day range from today when you tick "Expires in 15 Days". You can assign the respective selection from the filter pane for 4 of these visuals. I attached the file for you. 

If this does not help you, please share a sample file showing where it is going wrong and what you expect as results.

[LeightonG]

Ah ok I see how it works now. Thank you for the help.

This doesn't quite do what I need it to however. 

For the 15, 30 and 60 day displays, I don't want them to count each other.

For example, the 30 day should count everything from 16 days from today up to 30 days from today, so it will exclude the 15 day count.

Does that make sense? I don't think I can do this with the example you have provided?

[Fowmy]

@LeightonG 

Please replace the code inside the "Expiry Days" measure with the following.
Revised measure:

Expiry Days =
VAR __days =
    SELECTEDVALUE ( Expiry[Days] )
VAR __expirydays =
    TODAY () + __days
RETURN
    SWITCH (
        TRUE (),
        __days = 0,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Expires] > TODAY (),
                NOT ISBLANK ( 'Table'[Expires] )
            ),
        __days = 15,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Expires] <= __expirydays,
                'Table'[Expires] > TODAY (),
                NOT ISBLANK ( 'Table'[Expires] )
            ),
        __days = 30,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Expires] <= __expirydays,
                'Table'[Expires]
                    > TODAY () + 15,
                NOT ISBLANK ( 'Table'[Expires] )
            ),
        __days = 60,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Expires] <= __expirydays,
                'Table'[Expires]
                    > TODAY () + 30,
                NOT ISBLANK ( 'Table'[Expires] )
            )
    )

[LeightonG]

Fantastic! Thanks so much for your help with this.

I really appreciate it.

[Fowmy]

@LeightonG 

You are most welcome!

[Fowmy · Accepted solution]

@LeightonG 

When you use CALCULATE in a column multiple times, there is context transition, and circular dependency occurs.
I think the best way to solve your problem is using a disconnected table as follows. Please refer to the attached PBIX file.  I created a dummy date table, you can replace it with your expires column.

[LeightonG]

Hi Fowmy,

Many thanks for taking the time to read and respond to my post. Much appreciated!

I have reviewed the pbix file you kindly created and attached for me, and applied it to my dashboard.

I am still unable to split out the calculations to show in separate visuals though.

Please see this snip of my dashboard with what I'm trying to display.

Am I missing something obvious?