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

     



Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Anonymous 

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.


  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

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

       



      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Fantastic! Thanks so much for your help with this.

        I really appreciate it.