Custom Column for Relative Date Range Measures

[edtm]

What I am trying to do is to:

1) Calculate difference in unit sales between two date ranges
2) If units sold in date range 1 is less than units sold in date range 2, then a custom column is used to call this out with "Leakage".

This works if I use static range as below:

Dax for Product A  2022 is:

So, I wanted to do a similar analysis but using relative date range instead of statc date range.  The table looks like this:

Now, the units for the 2022 total uses this dax:

And, the units for 2023 total uses this dax:

The custom column that shows "Leakage" is thus:

However, there there should be "Leakage" labled for West region but it's blank.  I assume my dax in custom column above doesn't work with relative date ranges.  So, is there a way to get the Leakage custom column to show "Leakage" for Wetern Region in the table with relative date ranges?

Sample file is below:
Sample 

Thanks!

Update:

Adding what is ultimately what I am trying to achieve which is a filter that filters for Regions that have Decrease YOY Units in Product A and Increase YOY units in Product B which indicates possible conversion from Product A to B but using relative instead of static date DAX:

[v-heq-msft]

Hi @edtm ,

Based on your description, I can understand that you mean you want to dynamically determine the range and calculate the difference of the dates while making a comment on the positive and negative values. Unfortunately, the pbix file you provided cannot be opened. Please provide your complete data in table or excel format so that we can help you faster. Please hide sensitive information in advance.

Best regards,

Albert He

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

[edtm]

Thank you for looking into my question. 

I just tested downloading the sample file and was able to open it.  At first I was like... wat's this? but then there's the download button:

I am sure this didn't get past you but not sure why else the file can't be accessed. 

Here's the base table sample data I put together in excel.

Excel Sample 
However, it might not be useful as without the PBIX file you can't see how I am I using it.

(But, I guess you could come up with your own method.)

[v-heq-msft · Accepted solution]

Hi @edtm ，
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create 2 calendar tables

Calendar 1 = VALUES('Table'[Year])
Calendar 2 = VALUES('Table'[Year])

Create measures

Selected by Calendar 1 = 
CALCULATE(
    MAX('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Year] = SELECTEDVALUE('Calendar 1'[Year])
    )
)

Selected by Calendar 2 = 
CALCULATE(
    MAX('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Year] = SELECTEDVALUE('Calendar 2'[Year])
    )
)

Value1 vs value 2 = [Selected by Calendar 1] - [Selected by Calendar 2]

Leakage or not = 
IF(
    [Value1 vs value 2] < 0,
    "Leakage",
    "increase"
)

Use the values of the two calendar tables as separate slicers
Final output

Best regards,

Albert He

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