Calculate 30 60 90 Days based on Date slider

[DSwezey]

I have two tables: Calendar and Receivables.

The receivables has a row called "Sales Amount"

I want to calculate the Sales Amounts that fall within a Current, 30, 60, and 90+ day bucket. The catch is, I want to see the bucket tiers based on the most recent date selected.

For example, today is 11/9/2021 so if the date slider was set to today I'd want the buckets to be based on that day, but if i turned the slider back to 3/15/2021 I want to see the bucket tiers as if 3/25/2021 was "Today"

 This is currently my DAX for my Bucket aging measure:

Aging = IF( DATEDIFF('Calendar'[Date], TODAY(), DAY) <= 30, "30 Days",

IF( DATEDIFF('Calendar'[Date], TODAY(), DAY) <= 60, "60 Days", "90 Days+")

)

[Anonymous · Accepted solution]

Hi @DSwezey ,

Herer's sample for the solution.

Main table:

Canlendar table:

There's no relationship between tables.

Three measure:

30 Days = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]>=MAX('Calendar'[Date])-29&&[Date]<=MAX('Calendar'[Date])))

60 Days = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]>=MAX('Calendar'[Date])-59&&[Date]<=MAX('Calendar'[Date])))

90 Days = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]>=MAX('Calendar'[Date])-89&&[Date]<=MAX('Calendar'[Date])))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

The difficultly here is that calculated columns cannot be responsive to slicer, otherwise you'd be able to write something like this:

Aging =
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR Days = DATEDIFF ( 'Calendar'[Date], MaxDate, DAY )
RETURN
    SWITCH (
        TRUE (),
        Days <= 30, "30 Days",
        Days <= 60, "60 Days",
        Days <= 90, "90 Days",
        "90 Days+"
    )

Since you need it to be dynamic, you'll have to use measures instead of a calculated column. How exactly this works will depend on how you're using these buckets. What do the visuals you're using these buckets look like?

[DSwezey]

I am replicating a manual excel report. I want to show what the Current, 30, 60, and 90+ day values are when I select a date. 

[AlexisOlson]

OK, in this case, you can create separate measures for each of these rows.

For example,

30 to 60 Days =
VAR SelectedDate = SELECTEDVALUE ( 'Calendar'[Date] )
VAR StartDate = SelectedDate - 59
VAR EndDate   = SelectedDate - 30
RETURN
    CALCULATE (
        SUM ( Sales[Amount] ),
        DATESBETWEEN ( 'Calendar'[Date], StartDate, EndDate )
    )

[DSwezey]

After giving that a shot I was unsuccessful in getting any values to populate the matrix. 

30Days =

VAR SelectedDate = SELECTEDVALUE ( 'Calendar'[Date] )

VAR StartDate = SelectedDate - 30

VAR EndDate = SelectedDate - 1

RETURN

CALCULATE (

SUM ( 'ReceivablesTransactions'[Sales Amount] ),

DATESBETWEEN ( 'Calendar'[Date], StartDate, EndDate )

)

[AlexisOlson]

Did you select a date?

You might want to default to today by modifying to

VAR SelectedDate = SELECTEDVALUE ( 'Calendar'[Date], TODAY() )