Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Raj12
Helper III
Helper III

Calculate data based on DB table & historical excel file

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_0-1684334850481.png

 



3 REPLIES 3
Raj12
Helper III
Helper III

Can anyone please help me out on this

johnt75
Super User
Super User

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.

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

QCLINQPINQDINQJINQANI
22112
1321212
1981111
211211

1

 

Excel Historical data :

YearMonthTotal Quotes
2022January111
2022February121
2022March112
2022April191
2022May144
2022June211

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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