cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gerbs
Regular Visitor

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

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. 

 

Gerbs_0-1639584545598.png

 

Valuation Date Object IDMarket value
31-12-201910101       104.900.000
30-09-202010101       110.354.800
31-12-202010101       114.437.928
30-09-202110101       113.407.986
31-12-202110101       122.253.809
31-12-201910102       101.900.000
30-09-202010102       104.039.900
31-12-202010102       111.946.932
30-09-202110102       120.231.005
31-12-202110102       128.526.945
31-12-201910103       137.138.250
30-09-202010103       107.600.000
31-12-202010103       112.334.400
30-09-202110103       123.343.171
31-12-202110103       123.096.485
31-12-202010104       105.200.000
30-09-202110104       104.674.000
31-12-202110104       105.930.088
31-12-202010105       106.500.000
30-09-202110105       108.204.000
31-12-202110105       110.259.876
31-12-202110106         96.200.000

 

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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

OwenAuger_0-1639740744959.png

 

OwenAuger_1-1639741327415.png

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

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

OwenAuger_0-1639740744959.png

 

OwenAuger_1-1639741327415.png

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors