Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
I am reporting on a matrix visualisation with the following:
I would like to calculate a SUM based on 'fct_amt'[amount]' such that:
My current attempt at this measure is the following:
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?
Solved! Go to Solution.
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'))
Hi,
Share some data and show the expected result.
Hi Ashish, dummy pbix: https://drive.google.com/open?id=12jHWSy1D4d-Bts1WgmLippBzHd_V9Qjg
Desired results as per ss below:
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'))
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |