cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors