Forum Discussion
Helper ICalculate measure based on startdate from dimension table
hi, hope somebody loves a challenge;)
we would like to sum two measures based on company startdate..
1. revenue = CALCULATE([revenue] ,FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
2. revenue_source_external = CALCULATE ([revenue],revenue[date] = MAX ( revenue[date] ))
company table:
company | startdate
a 1-7-2025
b 1-1-2020
revenue table:
company | date | revenue | revenue_source_external| etc
if year/month selected = 2025-07 then we would like to see the following in a matrix, with in the rows the companies and in the columns year-month:
sum of company a: revenue_source_external until 1-7-2025 and from 1-7-2025 revenue
+
sum of company b: revenue from 2020 until now
we have got it working, but it is not showing totals. I cannot share pbix because of company info. Does anybode have a clue?
4 Replies
- lbendlin
Super User
1. You cannot use the transaction date from the dimension table. It must come from the fact table.
2. Use variables to protect your values from the CALCULATE context transition.
we have got it working, but it is not showing totals.Please elaborate.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
v-saisrao-msftCommunity Support
RH205Perfect, thnx!
- v-saisrao-msft
Hi RH205,
Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.
Thank you.
