Skip to main content
cancel
Showing results for 
Search instead 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

Reply
TG_12
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: 

TG_12_1-1664277158575.png

 

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. 

 

TG_12_2-1664277660387.png

 

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

 

Thanks

 

1 ACCEPTED SOLUTION
amitchandak
Super User
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 MTD Sales = CALCULATE([Q1],DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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
https://www.youtube.com/watch?v=6LUBbvcxtKA

View solution in original post

2 REPLIES 2
amitchandak
Super User
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 MTD Sales = CALCULATE([Q1],DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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
https://www.youtube.com/watch?v=6LUBbvcxtKA

Thanks Amit - appreciate the help! 

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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