Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    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.

     



11 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    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.

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Fowmy
        Super User
        5 years ago

        Anonymous 

        You can set the bracket as follows in the filters pane