Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 @Anonymous 

 

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!
Blog
Twitter
LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

Hi @Anonymous 

 

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!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors