Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

Desired results as per ss below:

 

image.png

Anonymous
Not applicable

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'))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.