DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days

[cuseman03]

Hi all...so I have a chart, let's call it chart A.  that chart is filtered on today's date because it's used as a monitoring chart for infrastructure incidents.  I have chart B which is supposed to show past 7 days.  Ideally, when something shows up in chart A that's bad, as it means it's a problem ONLY today.  I have a filter set to incident shows up in table if remaining disk space is <20%.  So let's say Server A shows up in Chart A.  Below this, in Chart B, I want to see that server's prior 7 day disk usage.  It does not work when I have Chart B set to relative date.  In every case, when I click on a server name in Chart A, all it shows me in Chart B is today's data.

I want to be able to see results (filtered on today's date) from Chart A and then drill in for values in Chart A to today's date -7 days for Chart B.  Literally everything I have tried ends up in Chart B as only showing data for today's date.  

the other issue is I have a parent child hirearchy in 'Server', which has many child 'Disk Drives'.  Violations that show up in Chart A are based on the storage space for 'Disk Drives', on today's date (Chart A).  I want to be able to click on the Hostname field, and have ALL drives for that server show me today -7 in Chart B (the drilled in chart).

 Chart A (based on Nov 8th)

Chart B ( Based on Nov 1-7th.  these are separate values, used for illustrative not supposed to match)

So Server 'A' has 5 disk drives.  Today, one of them went to 0% free.  Only for today was drive V in violation.  I want to have Chart B show me 7 day history for ALL of server A's 5 drives.

Thanks

[Anonymous]

Hi @cuseman03 ,

Can you provide the sample data? What is the FileDate column? What’s more, are there two tables or just one table of data?

Best Regards,

Wisdom Wu

[cuseman03]

this makes sense in theory but I'm struggling to get it to look right.  To be clear, I want Chart B not to sum the usage over 7 days but to show the usage across 7 days with the X axis being the last 7 days.  

my original DAX for getting percent free is 

DIVIDE(

    SUM('StorageSpaceUtilization'[Free(GB)]),

    SUM('StorageSpaceUtilization'[Size(GB)])

)

I then used this measure in your above recommendation

CALCULATE([FreeSpace], REMOVEFILTERS(StorageSpaceUtilization[FileDate]), DATESINPERIOD(StorageSpaceUtilization[FileDate].[Date], TODAY(),-6,DAY))

However the values look incorrect.  .  For Chart B, I should have 7 values for any given server I click on.  That's  the past 7 days. I can't get Chart A to disassociate and still show chart B correctly.  I want to have a line graph on Chart B of the below data

Also, how do i get Chart B to only display the drive that is affected.  This is tricky because it could be one to many.  For example, if in Chart A Server 4845 has 2 drives that have <20%, when I click on that I only want those two to show up on the below chart.  

[VahidDM]

Hi @cuseman03 

Issue:

When you select a server in Chart A (filtered to show today's data), Chart B only displays data for today instead of showing the last 7 days for that server. You want Chart B to display the 7-day history of all disk drives for the selected server, regardless of the date filter applied in Chart A.

Solution:

To achieve this, modify the DAX measure used in Chart B to:

• Ignore the date filter coming from Chart A.
• Apply a new date filter to show the last 7 days.
• Keep the server filter from the selection in Chart A.

Steps:

1. Create a New Measure for Chart B:

DiskUsage_Last7Days =

CALCULATE(

    [Disk Usage Measure],

    REMOVEFILTERS('Date'),

    DATESINPERIOD(

        'Date'[Date],

        TODAY(),

        -6,

        DAY

    )

)

Explanation:

• CALCULATE: Modifies the filter context for the measure.
• [Disk Usage Measure]: Your existing measure calculating disk usage.
• REMOVEFILTERS('Date'): Removes any date filters, including the one from Chart A.
• DATESINPERIOD(...): Applies a filter for the last 7 days starting from today.
• The server filter from Chart A remains active, so the measure only calculates for the selected server.
1. Replace the Measure in Chart B:
• Use DiskUsage_Last7Days as the value in Chart B.
• This ensures Chart B always shows the last 7 days for the selected server.
2. Verify Visual Interactions:
• Ensure that Chart A filters Chart B on the Server field.
• The date filter should not interfere due to the REMOVEFILTERS('Date') in the measure.

Alternate Measure (Based on Data's Latest Date):

If your data's latest date isn't today, adjust the measure to use the maximum date in your data:

DiskUsage_Last7Days =

VAR MaxDate = MAXX(ALL('Date'), 'Date'[Date])

RETURN

CALCULATE(

    [Disk Usage Measure],

    REMOVEFILTERS('Date'),

    DATESINPERIOD(

        'Date'[Date],

        MaxDate,

        -6,

        DAY

    )

)

Notes:

• Keep Filters on Server and Disk Drives:
• The measure retains filters on Server and Disk Drives from Chart A.
• Only the date filter is overridden.
• Multiple Selections:
• If multiple servers might be selected, consider using VALUES('Table'[Server]) and adjust the measure accordingly.
• Ensure Proper Relationships:
• Verify that your data model has correct relationships between the tables involved.

Conclusion:

By adjusting the DAX measure to ignore the date filter from Chart A and applying a new filter for the last 7 days, Chart B will display the historical disk usage for all drives of the selected server. This approach allows you to monitor current issues and investigate historical trends seamlessly.

Example of Final Measure:

DiskUsage_Last7Days =

CALCULATE(

    [Disk Usage Measure],

    REMOVEFILTERS('Date'),

    DATESINPERIOD(

        'Date'[Date],

        TODAY(),

        -6,

        DAY

    )

)

Use this measure in Chart B to achieve the desired functionality.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[cuseman03]

I'm unable to get this.  I'm thinking it is because I do not have a separate date table.  I am not sure.