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
Raj12
Helper II
Helper II

Measure to calculate data based on two sources : DB table & excel file for different time period

Need to calculate total quotes:

1) For current and last 13 months data is available in live DB

2) For period earlier than 13 month data is redacted from DB and hence need an excel file to fetch that data

 

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

Quote IDQPINQDINQJINQANI
22112
1321212
1981111
211211

1

3. Historical table hold all 2022 data

YearMonthTotal Quotes
2022January111
2022February121
2022March112
2022April191

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

 

2 REPLIES 2
lbendlin
Super User
Super User

Sounds straightforward. What have you tried and where are you stuck?

I created a measure but unable to get data chnage over the period of time:

Total Quotes =
var mon_start_date =     CALCULATE(Min(DateTable[Date] ),ALLSELECTED(DateTable[Date] ))
var mon_end_date =     CALCULATE(MaxDateTable[Date] ),ALLSELECTED(DateTable[Date] ))
var yr = VALUE(SELECTEDVALUE(DateTable[Date]))
var curr_monMONTH(TODAY())
var curr_yearYEAR(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'[QUOTE ID]),'DB'[QDATE]>=mon_start_date && 'MSQUREFR New'[QDATE]<=mon_end_date) )

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.