I have a fact table of donation activity. It can be thought of as no different to a sales fact table
- donation date
- donation amount
- donor details
In general I calculate all the usual measure:
- Total donations
- Cumulative donations
- YoY differences etc
What I need to do is be able to select a specific date and then see what the donations looked like on that date.
That is, I need to see the:
*Total donations up to that date
*Cumulative donations up to that date
*Total donations Last year up to that date
*Cumulative donations up to that date last year
Borrowing some techniques from https://www.youtube.com/watch?v=MooZofz5GOI&__s=6wttudxm5unrr3pc7opm I can allow a selection of the “As at date”
I can calculate the total donations to the as at date,
Selected Date = SELECTEDVALUE('Selection Date'[Date], MAX('Selection Date'[Date])) // If a date is not selected, then use the last date in the list As At Total Donor Receipts = VAR SelectedDate = [Selected Date] VAR SumDonorReceipts = CALCULATE( SUM('Donor Receipts'[Receipt Amount]), FILTER( ALLSELECTED('Donor Receipts'), 'Donor Receipts'[Receipt Date] <= SelectedDate ) ) RETURN IF(SumDonorReceipts = BLANK(), 0, SumDonorReceipts) As At Total Donor Recepts LY = CALCULATE( [As At Total Donor Receipts], SAMEPERIODLASTYEAR(Dates[Date]) )
however, SAMEPERIODLASTYEAR and the standard cumulative pattern breaks down.
I am thinking I will need to create a virtual table, but have not been able to figure out exactly what I need to do.