Forum Discussion

tony_tohme's avatar
tony_tohme
New Member
4 years ago
Solved

Stacked Chart from 2 tables

Hi there, I have 2 simple tables Table 1 Year Month Allocated Resources 2021 6 8 2021 7 2 2021 4 3   And Table 2 Year Month Hired 2021 2 4 2021 6 2 2021...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi tony_tohme ,

    The final output refer:

     

    create the column on the two table:

    yearmonth = 'Table'[Year]&""&'Table'[Month]

    Column = 'Table (2)'[Year]&""&'Table (2)'[Month]

    Then create the below table:

    Table3 =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            'Table',
            "year", 'Table'[Year],
            "month", 'Table'[Month],
            "my", 'Table'[yearmonth]
        ),
        SELECTCOLUMNS (
            'Table (2)',
            "year", 'Table (2)'[Year],
            "month", 'Table (2)'[Month],
            "my", 'Table (2)'[Column]
        )
    )
)

     

    Relationship like below:

     

    Then create the visual:

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

tony_tohme , Either create a common month year table or Date table and join with that and analyze other 

In both table you can have one of the column

Month Year = [Year]*100 +[Month]

 

or

 

Date = date([year],[month],1)

 

Join both with date table to month year and use that for analysis 

 


To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.