I have an Power BI assignment for a course with the data file containing Sales data and a column for DSO.
The requirments are below:
Build a Power BI dashboard with the attached “weekly cashflow report.xlsx”.
Please generate a date table.
Here are the metrics that need to be answered.
1. Number of days outstanding.
2. Invoiced and invoiced target by week ending.
3. Trailing 4 weeks invoiced AVG.
4. Trailing 4 week collected AVG.
5. Collected and collected target by week ending.
6. Days outstanding by week ending.
7. A/R balance by week ending.
8. AR Balance by week ending,
I have no idea how to do 1 and 6. Any advise would be appeciated.
Snip of data below:
| Week Ending | Collected | Invoiced | A/R Balance | Bank Balance | Collected Target | AVG Daily Sales | DSO | Invoiced Target | T4W Collected | T4W Invoiced |
| 2014/08/24 | $ 2 150 | $ 79 550 | $ 1 898 450 | $ 1 141 650 | $ 161 250 | | | $ 161 250 | | |
| 2014/08/31 | $ 1 231 950 | $ 73 100 | $ 1 165 300 | $ 2 227 400 | $ 161 250 | | | $ 161 250 | | |
| 2014/09/07 | $ 25 800 | $ 152 650 | $ 920 200 | $ 2 214 500 | $ 161 250 | | | $ 161 250 | | |
| 2014/09/14 | $ 391 300 | $ 17 200 | $ 634 250 | $ 2 438 100 | $ 161 250 | | | $ 161 250 | $ 412 800 | $ 80 625 |
| 2014/09/21 | $ 6 450 | $ 296 700 | $ 1 197 550 | $ 1 603 900 | $ 161 250 | | | $ 161 250 | $ 413 875 | $ 134 913 |
| 2014/09/28 | $ 90 300 | $ 2 150 | $ 1 120 150 | $ 1 556 600 | $ 161 250 | | | $ 161 250 | $ 128 463 | $ 117 175 |
| 2014/10/05 | $ 337 550 | $ 152 650 | $ 971 800 | $ 1 771 600 | $ 161 250 | | | $ 161 250 | $ 206 400 | $ 117 175 |
| 2014/10/12 | $ 217 150 | $ 169 850 | $ 875 050 | $ 1 821 050 | $ 161 250 | $ 16 854,46 | 51,9 | $ 161 250 | $ 162 863 | $ 155 338 |
| 2014/10/19 | $ 2 150 | $ 481 600 | $ 1 367 400 | $ 1 771 600 | $ 161 250 | $ 24 033,93 | 56,9 | $ 161 250 | $ 161 788 | $ 201 563 |
| 2014/10/26 | $ 83 850 | $ 103 200 | $ 1 161 000 | $ 1 661 950 | $ 161 250 | $ 24 571,43 | 47,3 | $ 161 250 | $ 160 175 | $ 226 825 |
| 2014/11/02 | $ 238 650 | $ 34 400 | $ 1 182 500 | $ 1 872 650 | $ 161 250 | $ 22 459,82 | 52,6 | $ 161 250 | $ 135 450 | $ 197 263 |
| 2014/11/09 | $ 258 000 | $ 47 300 | $ 965 350 | $ 1 904 900 | $ 161 250 | $ 22 997,32 | 42,0 | $ 161 250 | $ 145 663 | $ 166 625 |
| 2014/11/16 | $ 58 050 | $ 27 950 | $ 1 259 900 | $ 1 924 250 | $ 161 250 | $ 18 198,21 | 69,2 | $ 161 250 | $ 159 638 | $ 53 213 |
| 2014/11/23 | $ 4 300 | $ 376 250 | $ 1 315 800 | $ 1 758 700 | $ 161 250 | $ 24 878,57 | 52,9 | $ 161 250 | $ 139 750 | $ 121 475 |
| 2014/11/30 | $ 124 700 | $ 12 900 | $ 1 199 700 | $ 1 838 250 | $ 161 250 | $ 22 383,04 | 53,6 | $ 161 250 | $ 111 263 | $ 116 100 |
| 2014/12/07 | $ 30 100 | $ 172 000 | $ 1 330 850 | $ 1 722 150 | $ 161 250 | $ 22 421,43 | 59,4 | $ 161 250 | $ 54 288 | $ 147 275 |
