Donations as at a specific date

DAX DAX Calculations
1

Hi all
I have a fact table of donation activity. It can be thought of as no different to a sales fact table
It has

  • 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.

Thanks

2

I thought that https://youtu.be/g_3eLaKgeEQ might be able to help so I tried,

As At Total Donor Recepts LY = 
VAR SelectedDate  = [Selected Date]
VAR SelectedYear  = YEAR(SelectedDate)
VAR SelectedMonth = MONTH(SelectedDate)
VAR SelectedDay   = DAY(SelectedDate)
VAR TotalDonor = 
CALCULATE( SUM('Donor Receipts'[Receipt Amount]),
        FILTER(  ALL('Donor Receipts'), 
            'Donor Receipts'[Receipt Date] >= DATE(SelectedYear - 1, 1, 1) &&
            'Donor Receipts'[Receipt Date] <= DATE(SelectedYear - 1, SelectedMonth, SelectedDay)  )
RETURN
TotalDonor

However there seems to be a problem with

DATE(SelectedYear - 1, SelectedMonth, SelectedDay)

If I choose any date other than the 1st of the month I get an out of range error. For some reason the day component is not evaluating correctly.

According to https://msdn.microsoft.com/en-us/query-bi/dax/date-function-dax as long as I enter an integer (positive or negative) in any of the fields I will get a date.

If I hardcode the value (eg 35 which does not exist so it does to the next month) in, it works.

Thoughts?

3

One thing I’m finding a little confusing is what is the difference between these two?

In there wording they seem like exactly the same calculation? Both are really just cumulative totals aren’t they? And you want to show what is the cumulative total for any date selected…

*Total donations up to that date
*Cumulative donations up to that date

Same here

*Total donations Last year up to that date
*Cumulative donations up to that date last year

I think you are on the right track with this one.

Due to the context created from the date selection it can be difficult to do this any other way.

Here’s a pattern that creates cumulative totals

Cumulative Sales Example = 
SUMX(
    CALCULATETABLE(
        SUMMARIZE( Dates, Dates[Date],
       "CumulativeTotal", [Sales From Selected Date] ),
           FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ) ) ),
           [CumulativeTotal] )

See here for what it looks like from the example

image
image.png971×647 47.4 KB

Then if you want to calculate this for last year

First this was the original formula from the example

Sales From Selected Date = 
VAR SelectedDate = CALCULATE( MAX( 'Selection Date'[Date] ), ALL( Dates ) )

RETURN
IF( AND( MAX( Dates[Date] ) >  SelectedDate - [Day Number] , MAX( Dates[Date] ) <= SelectedDate ),
    [Total Sales],
          BLANK() )

To go to last year I change it to this by adding the - 365 to it

Sales from Selected Date LY = 
VAR SelectedDate = CALCULATE( MAX( 'Selection Date'[Date] ), ALL( Dates ) ) - 365

RETURN
IF( AND( MAX( Dates[Date] ) >  SelectedDate - [Day Number] , MAX( Dates[Date] ) <= SelectedDate ),
    [Total Sales],
          BLANK() )

Then I used the same formula pattern as earlier for cumulative last year, just changing the relevant formulas

Cumulative Sales Example LY = 
SUMX(
    CALCULATETABLE(
        SUMMARIZE( Dates, Dates[Date],
       "CumulativeTotalLY", [Sales from Selected Date LY] ),
           FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ) ) ),
           [CumulativeTotalLY] )

This all has seemed to work well

Uploading…

See how go with these ideas

4

Hi Sam,
Very close. I see where you are going with this however I was needing total sales up to the selected date rather than sales from the selected date.

I have it working for the current year, but not for the last year.

I have attached my sample file for your review. This will make it easier to see what I am talking about.

Thanks
As At Testing.pbix (553.4 KB)

5

Subtle change required

Sales up to selected date LY = 
VAR SelectedDate = CALCULATE( MAX( 'Selection Date'[Date] ), ALL( Dates ) )
RETURN
IF( MAX(Dates[Date]) <= SelectedDate,
    CALCULATE( [Total Sales],
        SAMEPERIODLASTYEAR(Dates[Date])),
    BLANK()
)

Cumulative now works as well
Thanks

6

Have looked into the model and have these results here

image
image.png999×256 16.8 KB

Here are all the formulas.

Sales up to selected date = 
VAR SelectedDate = CALCULATE( MAX( 'Selection Date'[Date] ), ALL( Dates ) )
RETURN
IF( MAX(Dates[Date]) <= SelectedDate,
    [Total Sales],
    BLANK()
)
    

Cumulative Sales Up to Selected Date = 
SUMX(
    CALCULATETABLE(
        SUMMARIZE( Dates, Dates[Date],
       "CumulativeTotal", [Sales up to selected date] ),
           FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ) ) ),
           [CumulativeTotal] )


Sales up to selected date LY = 
VAR SelectedDate = CALCULATE( MAX( 'Selection Date'[Date] ), ALL( Dates ) )
RETURN
IF( MAX(Dates[Date]) <= SelectedDate, [Total Sales LY], BLANK() )


Cumulative Sales To Selection LY = 
SUMX(
    CALCULATETABLE(
        SUMMARIZE( Dates, Dates[Date],
       "CumulativeTotalLY", [Sales up to selected date LY] ),
           FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ) ) ),
           [CumulativeTotalLY] )

See how you go with these

I’ve attached here

As At Testing.pbix (555.9 KB)

7

Thanks Sam
Looking good.
The introduction of BLANK() into the Sales up to selected date DAX means that the totals don’t work but I solved this with a virtual table as well.
Excellent work.

1 Like
8

That’s great. Chrs