Need to filter out old data from Access DB when I Load my fact table into Power BI

[Anonymous]

Hi! 

I'm working on a project where I have created an Access Database containing 3 months (and growing) of daily snapshot datasets that are originally generated in Excel from our ATS environment (CSV export). These daily files contain the complete job order history (small, new company) so there are alot of unnecessary rows of data that I do not need for analysis for my reports in Power BI. The excel file I use to import into Access also cleanses the data and makes the keys for the table joins on my dimension tables. (denormalizing some of the obvious data topics that are in the original download from source sys).

My questions are: 
1. Can I filter out in PowerQuery prior snapshots such that I only have the current snaphot data ? In Access, I have a column named 'snapshot_date' that I add to the daily snapshot file when I create my daily dashboard report in Excel.

2. Even further, can I filter out the snapshot data so that I create multiple fact tables such as 1 for current day data, 1 for prior day data and 1 for prior week data so that I can do daily and weekly comparisons? I don't think I'm strong enough with DAX to simply do the comparisons within DAX measures.

3. Or would it be better to summarize the jobs table in it's entirety by snapshot date? I have concerns here as I need to slice my data by multiple dimensions and I don't believe summarize would do the trick - as I would need to slice by date, customer, sales rep, status, BU, etc... 

The reason I'm asking is that the data is not always clean although that is being worked on now and some values are computations in the Excel file that is currently the source of all bookings data for company reports. Some of those columns I'm hesitant to replicate either in PowerQuery (not easy) or as custom columns in Power BI (which I know from experience can slow down performance).  Also, the data changes that I want to evaluate aren't just numerical (counts and booking value) over time - alot of it is driven by status on the job order. 

Any help or advice would be appreciated!

Thanks

Teresa Buchanan

Operations Data Analyst

[Anonymous]

Hi Amitchandek,
thanks for your responses and the links. I will take a look at them and see if these will help solve my modeling problems. 

[amitchandak · Accepted solution]

@Anonymous ,

1. Can I filter out in PowerQuery prior snapshots such that I only have the current snaphot data ? In Access, I have a column named 'snapshot_date' that I add to the daily snapshot file when I create my daily dashboard report in Excel.

1. If query is supported (Advance option), You can write a query and filter the data

2. You can filter data in power query using a date or date M parameter 

3. Define incremental settings and give archive duration 

2. Even further, can I filter out the snapshot data so that I create multiple fact tables such as 1 for current day data, 1 for prior day data and 1 for prior week data so that I can do daily and weekly comparisons? I don't think I'm strong enough with DAX to simply do the comparisons within DAX measures.

1. do not do that for the same table, use power bi time intelligence, you can always find last , first day of month , least day of the month using a date easily

refer previousday, previousmonth, datesmtd, closingbalancemonth , firstnonblankvalue, lastnonblankvalue

3. Or would it be better to summarize the jobs table in it's entirety by snapshot date? I have concerns here as I need to slice my data by multiple dimensions and I don't believe summarize would do the trick - as I would need to slice by date, customer, sales rep, status, BU, etc..

1. Bring data to a level where you want to use it. do not summarize , if that is not creating too much of data

prefer start schema 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

Firstnonblankvalue, lastnonblankvalue
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak