Forum Discussion
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
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=145sPower 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
2 Replies
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=145sPower 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=6LUBbvcxtKAThanks Amit - appreciate the help!
