Measure in a matrix to show Peak or Max Usage % from past 6 months

[rebecca_allison]

Hi All, I'm at my wit's end. In need of some urgent help to close out this project with this relatively simple scenario (at least at first).

In Short: Need to show in a matrix the Universal Peak Usage % from the past 6 months. ( Show what was the peak usage over the past 6 month from Today()) I have created a Tool Tip that shows a great sparkline over time with the same measure, but I am struggling to get the correct Peak/Max to show within my timeframe within the equation. 

Notes:

- The Measure I call the "Universal Peak Usage %"  is based on the Date field called "DateDim[Date]"

- The Measure in summary, calls a certain product's pre-made usage measure based on the filter on it's name - "new_product[new_name]". I've used a SWITCH Statement for this instead of IF. 

- The measure is in a Matrix that is connected to a Time Slicer - Date field is called "Orginal Renewal Date" - it shows you the list of companies and products that Renew within the selected month - this is not the same date field for my measure and should be kept separate with no impact. To my knowledge they are not connected in anyway. 

- Need to write the Filter/Slicer within the Measure - (i think)

- I do not have access to my data model - I'm limited to only work with creating new measures, so I can't create new columns.

Here is my Measure formula: I'm more interested in my RETURN equation. How do I tell it to select the Peak. I've attempted some Max scenarios, but again my Peak Usage Ratio's are all Measures. 

Universal Peak Usage % = 
VAR EN_Ratio =
    CALCULATE ( [Peak Usage Ratio %], new_product[new_name] = "enersight")
VAR VN_Ratio =
    CALCULATE (
        [Peak Usage Ratio %],
        new_product[new_name] = "Val Nav",
        CRM_LICENSE_DATA[PRODUCT] = "VALNAV",
        LICENSE[PRODUCT] = "VALNAV"
    )
VAR EM_Ratio =
    CALCULATE ([Peak Usage Ratio %],
        new_product[new_name] = "esi.manage",
        CRM_LICENSE_DATA[PRODUCT] = "ESIMANAGE",
        LICENSE[PRODUCT] = "ESIMANAGE"
    )
VAR RTX_Ratio =
    CALCULATE ( [RTXRatio], new_product[new_name] = "Execute - RTX")
VAR Budget_Ratio =
    CALCULATE ( [BudgetRatio], new_product[new_name] = "Execute - Budget")
VAR AFE_Ratio =
    CALCULATE ( [AFERatio], new_product[new_name] = "Execute - AFE")
VAR Select_Product =
    SELECTEDVALUE ( new_product[new_name] )

RETURN
 IF (
            HASONEVALUE( Account[Account Name] ) ||
            HASONEVALUE(Account[Parent Customer] )||
            HASONEVALUE(new_product[new_name] )
        ,
            CALCULATE (
            SWITCH (
                Select_Product,
                "Execute - RTX", RTX_Ratio,
                "Execute - Budget", Budget_Ratio,
                "Execute - AFE", AFE_Ratio,
                "enersight", EN_Ratio,
                "Val Nav", VN_Ratio,
                "esi.manage", EM_Ratio,
                BLANK ()
            ), ALLEXCEPT(DateDim, DateDim[Date]), FILTER(DateDim, DateDim[Date] >= EDATE(TODAY(), -6))
            )
        )

Below are the screenshots they show two examples where the % displayed in the Column called "Peak Usage % in Last 6 Mnths" (this is the Universal Peak Usage % Dax equation I posted above, it is also used in the tooltip and in the table to the right) is not the peak within those 6 months. The Tooltip is filtered DateDim[Date] - Advanced Filter: In the last 6 Months. 
Example 1: peak usage % should show 100%
Example 2: Peak usage % should show Also 100%, from September. 

I've included the smaller table to show you that it is calling the correct numbers. 

Thanks in advance for your help. I know dangerously just enough to get myself stuck and I have no idea how easy or how difficult it is to figure this one out. 

I appreciate all the help (as fast as possible)

Best!

[v-eachen-msft · Accepted solution]

Hi @rebecca_allison ,

You could refer to the following DAX and add your own time filter in it.

Peak Usage % in last 6 months=
DIVIDE (
    COUNTX ( FILTER ( 'Table', [Universal Peak Usage %] = 1 ), [Universal Peak Usage %] ),
    COUNTX ( 'Table', [Universal Peak Usage %] )
)