cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Clustered column chart combining two data sets

Hi, I have now automated a process to count the number of email threads that are received each month into a mailbox. Prior to 2022 I calculated this number manually and I would like to have a clustered column chart to show the month by month increase over 2020, 21 & 22.

What I have is one table containing the numbers from each month for 2020 & 21:

I also have the data that now feeds in automatically from the mailbox. Within this I have a calculated column that copies the first occurence of an email subject and I have a column of DateTimeRecieved - when I do a distinct count on this column split up into months of DateTimeRecieved I get the correct numbers for 2022 that I need to add to the 2020 and 2021 numbers . I have created clusterted charts for each of these tables seperately but I cannot figure out how to join these together.

Any help would be great as I have stuck for a while now.

Thanks

1 ACCEPTED SOLUTION
Super User

@TG_12 , Create a common date table and join with both tables . Create a combined measure

Q1= Sum(Table202021[No of queries]) +Sum(Table2022[No of queries])

Then you can use TI with date table

MTD Sales = CALCULATE([Q1],DATESMTD('Date'[Date]))
last month Sales = CALCULATE([Q1],previousmonth('Date'[Date]))

last year MTD Sales = CALCULATE([Q1],DATESMTD(dateadd('Date'[Date],-12,MONTH)))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

2 REPLIES 2
Super User

@TG_12 , Create a common date table and join with both tables . Create a combined measure

Q1= Sum(Table202021[No of queries]) +Sum(Table2022[No of queries])

Then you can use TI with date table

MTD Sales = CALCULATE([Q1],DATESMTD('Date'[Date]))
last month Sales = CALCULATE([Q1],previousmonth('Date'[Date]))

last year MTD Sales = CALCULATE([Q1],DATESMTD(dateadd('Date'[Date],-12,MONTH)))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Frequent Visitor

Thanks Amit - appreciate the help!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors