Create a dynamic summary for backlog cases and current cases

[Anonymous]

Hello Team,

Once again i need some help with Power Bi. I have 2 connected tables to QuickBase, one of them contains information of number of cases in previous 3 months, second table contains all cases since beggining of the year. I want to create a single chart where i can show case count for previous 3 months based on 1st table, and case count for current month based on 2nd table. My idea is to create a summary table which will hold values that chart will use to visualise data. To visualise it better:

1st Table

Month End	Product	LoB	New	Closed
31-01-2020	Box	Sales	3	26
31-01-2020	Shapes	Market	6	11
29-02-2020	Widget	Net	1	2
31-03-2020	Can	Food	17	7

2nd Table

Date Case Created	Product	LoB	IsClosed	Case Owner
01-02-2020	Can	Food	FALSE	Tom Jerry
28-03-2020	Widget	Net	TRUE	Jack Sparrow
05-04-2020	Box	Sales	FALSE	Case Owner
05-04-2020	Shapes	Market	FALSE	Case Owner

I decided to create 3rd table where type equals to Backlog for previous 3 months:

Month	Month#	Type
January	1	Backlog
February	2	Backlog
March	3	Backlog
April	4
May	5
June	6
July	7
August	8
September	9
October	10
November	11
December	12

I extracted month name from [Month End] (1st table) and [Date Case Created] (2nd table) to new column in each table. So all tables have column "Month" with month names. And now what i am struggling with, how do i summarize it?

I am trying to build DAX function for 3rd table like this:
Column Name = If('3rd Table'[Type]="Backlog",Count('1st Table'[New] WHERE '1st Table'[Month]='3rd Table'[Month]'),Count('2nd Table'[isClosed] WHERE '2nd Table'[Month]='3rd Table'[Month]'))

I know syntax is no correct, i just wanted to describe my approach for it.

Thanks in advance for any help.
Ksusser

[Greg_Deckler]

I would skip the summary table and instead create a Date table using CALENDAR or CALENARAUTO. Link the Date columns together for these 3 tables. Then you can simply use default aggregations in your visual.

[Anonymous]

Thanks for response,

I am not sure if calendar table will work cuz my end goal is to create bar chart with certain conditions.
Where:
data from January, February and March will come from 1st table

but data for April will come from 2nd table

And this should always work for the previous 3 months and the current month.

I think summary table is a must in this case.

Regards,
Ksusser