Posts Weekly Oracle database results into Column / Row

[dcg38524]

Hello,

Seeking advice if there is a way to automate the following manual steps:

Scenario: We are successfully collecting various Oracle database data on a weekly basis using the Power BI tool.   In this one case because of lack of DAX knowledge we are manually entering weekly results into a column each Thursday.

Question: Is there a way for us to automate this process, so that each Thursday update the collected data would place the results into the next available row labeled that Thursday’s date?

Thank you in advance for any help,

Don

The Power BI table name is "SL_Results"

Example below: The "Amount" & "Closed" columns have empty rows slotted for Thursday's weekly updated which is currently being performed manually.

Week#	Weekly Date	Weekly Cumulative Target	Amount	Closed
24	13-Jun-2019	1858	1484	737
25	20-Jun-2019	1827	1404	722
26	27-Jun-2019	1796	1244	880
27	4-Jul-2019	1766	1137	863
28	11-Jul-2019	1735	1145	785
29	18-Jul-2019	1704	1065	878
30	25-Jul-2019	1674	965	828
31	1-Aug-2019	1643	874	918
32	8-Aug-2019	1613	812	688
33	15-Aug-2019	1582	806	711
34	22-Aug-2019	1551	853	505
35	29-Aug-2019	1521	837	766
36	5-Sep-2019	1490	819	512
37	12-Sep-2019	1459	616	930
38	19-Sep-2019	1429	556	649

[Icey · Accepted solution]

Hi @dcg38524 ,

Do you mean that Thursday is the first day of one week? If so, maybe you could try this:

Amount = 
CALCULATE (
    SUM ( 'Table'[Number] ),
    'Table'[Date] >= EARLIER ( 'SL_Results'[Date] )
        && 'Table'[Date]
            < EARLIER ( 'SL_Results'[Date] ) + 7
)

Closed = 
CALCULATE (
    SUM ( 'Table'[Number] ),
    'Table'[Date] >= EARLIER ( 'SL_Results'[Date] )
        && 'Table'[Date]
            < EARLIER ( 'SL_Results'[Date] ) + 7,
    'Table'[Status] = "Closed"
)

This is my PBIX file.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dcg38524]

Icey,  your help and guidance in this matter is much appreciated .

Thank you,

Don

[Icey]

Hi @dcg38524 ,

Maybe you can create relationships between the two tables and create 'Amount' and 'Closed' columns using DAX.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dcg38524]

Thanks Icey,  would you mind sharing the DAX code you kindly suggested which will accomplish addending the "Amount" & "Closed" columns weekly?

[parry2k]

@dcg38524 not sure if I fully understood the current process. If your power bi file directly reading data from Oracle table then everytime you refresh power bi report, it will get full data from oracle with all new records. Not sure if this answered your question or I'm missing something here.

[dcg38524]

Hello parry2k,

Apology, for any missunderstanding - 

• Current we have 2x tables:

1. 1x table runs the weekly Oracle query (In this case a SQL "count" statement)
2. The other table we created and update result from the above SQL query manually.

-Don