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

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