Forum Discussion
Helper IIIMeasure 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 ID | QPIN | QDIN | QJIN | QANI |
| 2 | 2 | 1 | 1 | 2 |
| 132 | 1 | 2 | 1 | 2 |
| 198 | 1 | 1 | 1 | 1 |
| 211 | 2 | 1 | 1 | 1 |
3. Historical table hold all 2022 data
| Year | Month | Total Quotes |
| 2022 | January | 111 |
| 2022 | February | 121 |
| 2022 | March | 112 |
| 2022 | April | 191 |
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
- lbendlin
Super User
Sounds straightforward. What have you tried and where are you stuck?
Raj12I 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(Max( DateTable[Date] ),ALLSELECTED(DateTable[Date] ))var yr = VALUE(SELECTEDVALUE(DateTable[Date]))var curr_mon= MONTH(TODAY())var curr_year= YEAR(TODAY())returnif (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) )
