Forum Discussion

edtm's avatar
edtm
Frequent Visitor
2 years ago
Solved

Custom Column for Relative Date Range Measures

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:

 




 

 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      edtm
      Frequent Visitor
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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