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.

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
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.

 

  • giogio's avatar
    giogio
    New Member
    6 years ago

    Thank you very much v-eachen-msft !
    As the tables contained much more information I also used some conditional formatting column iterations in the edit queries tab with great results!

    Question on this: is there something similar but more flexible than chords visuals? It's fine but maybe you have in mind something more useful to show these data in a visual form.

     

    Thanks again!