DAX: Creating labels with FIRSTDATE and a date filter or dropdown

[Gerbs]

Hi Everyone, 

I'm new to Power BI and can't figure out how to solve the following issue: 

I have a list of real estate assets that are valuated each quarter. Within the dashboard I want to be able to analyse all the valuation data for each quarter (recent and historical). One important aspect of the analysis is to flag an asset if it is new to the portfolio ('acquisition').

In order to do so, I want to use a slicer or dropdown to select the valuation date that shows the specific KPI's for that quarter and group assets as acquisitions when the date = firstdate of valuation date. I've tried a lot of solutions today, but all of them results in adding multiple extra rows to my table. 

Please see below a little mock-up in Excel and a sample of the data. 

Valuation Date	Object ID	Market value
31-12-2019	10101	104.900.000
30-09-2020	10101	110.354.800
31-12-2020	10101	114.437.928
30-09-2021	10101	113.407.986
31-12-2021	10101	122.253.809
31-12-2019	10102	101.900.000
30-09-2020	10102	104.039.900
31-12-2020	10102	111.946.932
30-09-2021	10102	120.231.005
31-12-2021	10102	128.526.945
31-12-2019	10103	137.138.250
30-09-2020	10103	107.600.000
31-12-2020	10103	112.334.400
30-09-2021	10103	123.343.171
31-12-2021	10103	123.096.485
31-12-2020	10104	105.200.000
30-09-2021	10104	104.674.000
31-12-2021	10104	105.930.088
31-12-2020	10105	106.500.000
30-09-2021	10105	108.204.000
31-12-2021	10105	110.259.876
31-12-2021	10106	96.200.000

[OwenAuger · Accepted solution]

Hi @Gerbs 

There are different ways of handling this, but I would recommend below (sample PBIX attached):

• Setting up your model with a Valuation fact table, and Asset, Date, and Status dimension tables.
• The Asset table contains each Object ID along with its FirstDate
  • FirstDate is derived in Power Query, by grouping the original Valuation table by Object ID and calculating the minimum Valuation Date.
• The Date table contains appropriate date columns (including quarter identifiers).
• The Status table is a simple dimension table for Status.
• The Valuation table contains the columns you posted above, plus a Status column derived in Power Query.
  • Status is found by joining the raw Valuation data with Asset, and conditionally setting status based on whether Valuation Date = FirstDate.
• Since FirstDate is a column of the Asset dimension, and Status is a column in the Status dimension, they can easily be included in any visuals, without needing any complicated DAX.

Hopefully that's useful as a starting point 🙂

Regards,

Owen