How to Import data only for the current date and current week in power bi ?

[Ayush_tiwari08]

Hi Everyone i have received a requirement in which i have to import data from oracle database via Direct querry but the issue is the data should get imported only for the current week and current day how i can acheive it ?

[amitchandak · Accepted solution]

@Ayush_tiwari08 , if you can create a date table having week rank in oracle or a date table in import mode 

Then you can have columns and measure like

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

[v-henryk-mstf]

Hi @Ayush_tiwari08 ,

Whether the advice given by @amitchandak  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Ayush_tiwari08 · Accepted solution]

@amitchandak  your answer was also correct but i did something like this and it worked 

Sql Querry for currnet week data = SELECT APPS.table_name.TRANSACTION_DATE AS DATE1,APPS.table_name.ITEM_NUMBER, FROM APPS.table_name
WHERE (APPS.table_name.TRANSACTION_DATE BETWEEN NEXT_DAY(TRUNC(sysdate-7),'MONDAY') AND TRUNC( sysdate +1) ) ORDER BY DATE1

SQL Query for current day data = SELECT APPS.table_name.TRANSACTION_DATE AS DATE1,FROM APPS.table_name WHERE (APPS.table_name.TRANSACTION_DATE >= TRUNC(sysdate)) ORDER BY DATE1

[amitchandak · Accepted solution]

@Ayush_tiwari08 , if you can create a date table having week rank in oracle or a date table in import mode 

Then you can have columns and measure like

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))