Calculate measure based on startdate from dimension table

[RH205]

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?

[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.

[v-saisrao-msft · Accepted solution]

Hi @RH205,

Thank you @lbendlin, for your insights.

I reproduced your issue with sample data and was able to create the issue in Power BI. I've attached the PBIX file for your reference.

Thank you.

[RH205]

Perfect, thnx!

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...