cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jhorbino Frequent Visitor
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
jhorbino Frequent Visitor
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'))

View solution in original post

3 REPLIES 3
Super User IV
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
https://www.linkedin.com/in/excelenthusiasts/
jhorbino Frequent Visitor
Frequent Visitor

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

Hi Ashish, dummy pbix: https://drive.google.com/open?id=12jHWSy1D4d-Bts1WgmLippBzHd_V9Qjg

 

Desired results as per ss below:

 

image.png

jhorbino Frequent Visitor
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'))

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

January 2020 Community Highlights

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

Top Solution Authors