Using a date slicer to determine the age of a record

[CLS479]

Hello,

I am trying to use a date slicer (not related to my main data table) to calculate the age of a record. Here is what my outcome shows in Excel and I would like to duplicate it in PowerBI. I can get the  Pivot to replicate in PBI, however the dispersion table needs to be able to give me the count of date ages AS OF the date that I select with a slicer.  I've been looking through threads all day and cannot find exactly what I need. Any help is appreciated.

[Anonymous]

Hi  @CLS479 ,

I created some data:

Age Table:

Date Table:

Here are the steps you can follow：

1. Create calculated column.

Year Month =
FORMAT(
    'Date'[Date],"mmm") &"-"&FORMAT('Date'[Date],"yy")

Max_date =
MAXX(
    FILTER(ALL('Date'),
    'Date'[Year Month]=EARLIER('Date'[Year Month])),[Date])

2. Create calculated table.

Using the Date table's [Year Month] as a Column will sort alphabetically by default, so create a new table and sort by date.

Table =
SUMMARIZE('Date','Date'[Year Month],'Date'[Max_date])

3. Joining a relationship between two tables.

4. Select [Year Month] – Column tools – Sort by column – [Max_date]

5. Create measure.

Count =
COUNTX(
    FILTER('Date',
    'Date'[Year Month]=MAX('Date'[Year Month])),[LoctionID])

Test =
var _maxdate=
MAXX(
    FILTER(ALL('Date'),'Date'[Year Month]=MAX('Date'[Year Month])),[Date])
return
IF(
   MAX('Date'[Max_date])<=DATE(2023,1,30),[Count],BLANK())

6. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[CLS479]

Thank you for your response, however this solution is not exactly what I am looking for. 

[Ashish_Mathur]

Hi,

I am not sure of how you generated the Dispersion table.  Share the download link of the PBI file with the formulas already written in the dispersion table.

[CLS479]

The dispersion table was manually created in Excel. Each month, the totals in the dispersion table are manually added. I am trying to re-create it in PowerBI by using a date filter to get a snapshot of what the dispersion numbers would be with an AS OF view. So if I wanted to see the totals AS OF January 30, 2023, it would show those totals and so on.

[Ashish_Mathur]

Hi,

I can help further only when you share what i requested in my earlier message.

[danextian]

Hi @CLS479 ,

Please see attached pbix you can reference. The concept is very similar as your use case if not the same.

[CLS479]

Hello @danextian 

Thank you for your response. This is almost exactly what I was looking for. The only difference is that I am looking at the age in months not years. How could I change the tables/measures to count the age in months instead?