Forum Discussion
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 p...
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
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] )
)
)
Fantastic! Thanks so much for your help with this.
I really appreciate it.
Anonymous
You are most welcome!