Forum Discussion

giogio's avatar
giogio
New Member
6 years ago
Solved

Data comparison over time

Hello All! I am struggling with the following problem: I have two tables representing listing of items at two different timeframes (Year 1 and Year 2). Every item is unique and represented by a seri...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi giogio ,

     

    You could merge these two tables with "full outer" mode.

    Then use three measures to get the results.

    FLOWS =
VAR a =
    SELECTEDVALUE ( Merge1[FROM] )
VAR b =
    SELECTEDVALUE ( Merge1[TO] )
RETURN
    IF (
        a <> BLANK (),
        IF ( b = BLANK (), "RETIREMENTS", IF ( a = b, "SAME", "CHANGE" ) ),
        "NEW"
    )
    # =
CALCULATE (
    COUNT ( Merge1[Custom] ),
    ALLEXCEPT ( Merge1, Merge1[FROM], Merge1[TO] )
)
    DETAIL = 
CONCATENATEX(Merge1,Merge1[Custom],",")

    Here is my test file for your reference.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

You should try using three functions in the formula except, intersect and union as per need

 

https://docs.microsoft.com/en-us/dax/except-function-dax

https://docs.microsoft.com/en-us/dax/intersect-function-dax

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601