Calculate data based on DB table & historical excel file

[Raj12]

Calculate data based on live table & historical excel file.

Need to calculate total quotes:

1) For last 13 months data is available in DB

2) For period earlier than 13 month data is redacted from DB.

Can we somehow store the data fetched in Power Bi recursively?

If not then need to create a measure that will give total quotes from 2022 to till date:

DB will have data from Feb 2022 onwards but for Jan 22 it will be available in static excel file.

Table deatils:

1. DateTable is centralized table with all the calender date

2. DB is the data base table that hold current & last 13 month data

3. Historical table hold Jan 2022 data

Measure to be created that shows data for Jan 2022 from hostorical excel table and remaining month from DB and this will change as next month it should show Jan, Feb data from hostorical excel table and remaining from DB....

Total Quotes_test =

var mon_start_date =     CALCULATE(Min(DateTable[Date] ),ALLSELECTED(DateTable[Date] ))

var mon_end_date =     CALCULATE(Max( DateTable[Date] ),ALLSELECTED(DateTable[Date] ))

var yr = VALUE(SELECTEDVALUE(DateTable[Date]))

var curr_mon= MONTH(TODAY())

var curr_year= YEAR(TODAY())

return

if(DATE(curr_year-1,curr_mon,31)<=mon_start_date,CALCULATE(SUM('Historical Data Table'[Total Quotes]),'Historical Data Table'[Year]=curr_year-1 && 'Historical Data Table'[Month]=curr_mon),

CALCULATE(DISTINCTCOUNT('DB'[QCLIN]),'DB'[QDATE]>=mon_start_date && 'MSQUREFR New'[QDATE]<=mon_end_date) )

[Raj12]

Can anyone please help me out on this

[johnt75]

You could use Power Query to append the tables during the data load process so you'd just have a date table and a fact table and you don't need to worry about where the data is coming from.

[Raj12]

I can not merge both the table as both table have totall different structure.
Historical file will be static table with just total Total Quotes in a month number whereas DB table is detailed table with records of each client data in each row something like below:

DB Table

QCLIN	QPIN	QDIN	QJIN	QANI
2	2	1	1	2
132	1	2	1	2
198	1	1	1	1
211	2	1	1	1

Excel Historical data :

Year	Month	Total Quotes
2022	January	111
2022	February	121
2022	March	112
2022	April	191
2022	May	144
2022	June	211