Hi everybody,
This is my first time posting, so please bear with me if i am doign something wrong.
I am trying to recreate an AR ageing report as per below which is done on excle. I have a transactions table which records all transactions. I went ahead & created a custom column using the transaction date to calculate ageing (days), then used the conditional formatting in power query to name age buckets as in table below.
But the problem is when i run the PBI visual (matrix table) & despite using a Running Total formula (Desc using transaction date), i still cannot get closing end of month totals for the last 12 months to show as in the below table;
| Mth | Total (Rs) | Current (Rs) | 1Mth | 2Mths | 3Mths | 4-6Mths | 7-9Mths | 10-12Mths | >12Mths |
| Sept 20 | 10,322,647.20 | 3,603,847.61 | 3,655,104.01 | 1,358,273.77 | 517,091.58 | 265,386.03 | 829,284.41 | - | 93,659.79 |
| Oct 20 | 12,131,481.95 | 3,224,416.34 | 3,352,538.10 | 3,024,782.20 | 1,208,540.89 | 281,958.79 | 789,797.46 | 156,183.80 | 93,264.37 |
| Nov 20 | 10,186,211.97 | 3,494,203.15 | 2,932,816.01 | 1,860,840.98 | 454,985.02 | 714,933.01 | 436,861.13 | 198,308.30 | 93,264.37 |
| Dec 20 | 11,831,628.18 | 2,679,662.38 | 3,191,964.75 | 2,478,492.42 | 1,816,335.66 | 1,040,597.39 | 226,940.71 | 304,370.50 | 93,264.37 |
| Jan 21 | 9,419,996.71 | 1,816,273.02 | 2,416,637.54 | 2,580,615.54 | 667,264.14 | 1,299,050.85 | 128,780.45 | 390,404.01 | 120,971.16 |
| Feb -21 | 10,271,670.12 | 2,468,830.60 | 1,609,942.25 | 1,911,141.90 | 2,029,649.06 | 1,259,339.80 | 481,391.34 | 258,129.86 | 253,245.31 |
| Mar -21 | 9,478,260.94 | 416,647.80 | 2,438,037.05 | 1,177,451.68 | 1,600,365.48 | 2,701,376.58 | 629,257.55 | 217,729.21 | 297,395.59 |
| Apr-21 | 8,894,243.93 | 1,646,116.40 | 391,345.50 | 2,111,541.59 | 942,567.78 | 2,247,984.85 | 959,244.71 | 119,568.95 | 475,874.15 |
| May 21 | 8,176,898.25 | 2,069,449.96 | 1,529,590.23 | 336,798.12 | 1,513,880.04 | 1,188,510.85 | 746,268.01 | 374,498.66 | 417,902.38 |
| June 21 | 8,696,679.66 | 2,837,580.18 | 1,751,148.65 | 964,488.19 | 100,217.88 | 1,520,054.18 | 637,817.61 | 428,219.94 | 457,153.03 |
| Jul 21 | 9,974,194.06 | 3,322,079.33 | 2,573,766.49 | 811,399.18 | 501,339.67 | 1,207,511.56 | 355,602.01 | 678,140.24 | 524,355.58 |
| Aug 21 | 10,308,108.39 | 3,030,756.43 | 3,026,651.23 | 1,594,221.63 | 174,239.55 | 930,873.86 | 327,899.49 | 488,713.94 | 734,752.26 |
| Sept 21 | 11,225,881.01 | 2,885,229.29 | 2,569,713.02 | 2,011,022.45 | 1,213,417.99 | 399,328.50 | 819,855.15 | 499,590.42 | 827,724.19 |
Can anyone please help me with get it right?
Thank you in anticipation.
BhimalL
