Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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. 

 

 

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

 

 

 

DAX
Filter
firstdate
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    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

     

     

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

    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