Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Icey, your help and guidance in this matter is much appreciated .
Thank you,
Don
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.
Thanks Icey, would you mind sharing the DAX code you kindly suggested which will accomplish addending the "Amount" & "Closed" columns weekly?
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Apology, for any missunderstanding -
-Don
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |