cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate date specific measure with another table's dates.

Apologies if I have missed a forum post on this question.

I have a a calendar dates table dim_dates linked to a fact table fct_amt on the date field.

• dim_dates has every date
• fct_amt only contains dates with values against them
• ie. Weekends are generally not in the table as the amounts are weekday based
• The two tables share a many to one relationship (on the left is dim_dates).

I am reporting on a matrix visualisation with the following:

• 'dim_dates'[dates] on Rows
• 'fct_amt'[amount] on Values
• I also have a slicer set to 'fct_amt '[dates]'.

I would like to calculate a SUM based on 'fct_amt'[amount]' such that:

1. If 'fct_amt'[dates] is not blank then SUM all the 'fct_amt'[amount]' for the current row's  'fct_amt'[dates]
2. If 'fct_amt'[dates] is blank then SUM all the 'fct_amt'[amount]' for the next available 'fct_amt'[dates]

My current attempt at this measure is the following:

1. Retrieve the closest available 'fct_amt'[dates] for the current 'dim_dates'[dates] (such that 'fct_amt'[dates]>='dim_dates'[dates])
2. SUM 'fct_amt'[amount]' for 'fct_amt'[dates] that matches step 1

I've got step 1 down and validated this by creating a measure based on the logic and it correctly shows the next available 'fct_amt'[dates] if 'fct_amt'[dates] is blank.

Now I've tried setting the slicer onto a date where 'fct_amt[dates]' is blank and have tried the following to no avail:

CALCULATE(SUM('fct_amt'[amount]),FILTER(ALL('fct_amt'[dates]),'fct_amt'[dates]=DateFromStep1)).

What step/s am I missing from my calculation?

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Calculate date specific measure with another table's dates.

The following DAX has worked for my desired solution though am unsure of the efficiency of it.

```amount_sum =
VAR ClosestAvailableDate =
CALCULATE(
FIRSTDATE('dim_dates'[date])
,FILTER(
ALLSELECTED('dim_dates'[date]),'dim_dates'[date]=
CALCULATE(FIRSTDATE('fct_amount'[date])
,FILTER(ALL('fct_amount'[date]),'fct_amount'[date]>=MAX('dim_dates'[date])))
))

RETURN
CALCULATE(SUMX(FILTER('fct_amount','fct_amount'[date]=ClosestAvailableDate),'fct_amount'[amount]),ALL('fct_amount'))```
3 REPLIES 3
Super User IV

## Re: Calculate date specific measure with another table's dates.

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Calculate date specific measure with another table's dates.

Desired results as per ss below:

Frequent Visitor

## Re: Calculate date specific measure with another table's dates.

The following DAX has worked for my desired solution though am unsure of the efficiency of it.

```amount_sum =
VAR ClosestAvailableDate =
CALCULATE(
FIRSTDATE('dim_dates'[date])
,FILTER(
ALLSELECTED('dim_dates'[date]),'dim_dates'[date]=
CALCULATE(FIRSTDATE('fct_amount'[date])
,FILTER(ALL('fct_amount'[date]),'fct_amount'[date]>=MAX('dim_dates'[date])))
))

RETURN
CALCULATE(SUMX(FILTER('fct_amount','fct_amount'[date]=ClosestAvailableDate),'fct_amount'[amount]),ALL('fct_amount'))```

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors