cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Reporting different counts on a date column

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.
1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
11 REPLIES 11
Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

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?

Super User

@LeightonG

You can set the bracket as follows in the filters pane

Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

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?

Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

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?

Super User

@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] )
)
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

Fantastic! Thanks so much for your help with this.

I really appreciate it.

Super User

@LeightonG

You are most welcome!

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors