Count Number of Days from Selected year to Last update

[ajisharavind_99]

Hi

I am looking for a solution to count the number of days since the last update date. I have a slicer for years (2020, 2021, 2022, etc.), and when I select the year , I need to calculate the number of days from the last date of the selected year to the last update by Team.

See the sample

[v-binbinyu-msft]

Hi @ajisharavind_99 ,

There is an another solution for you, different with @barritown , need not create a large date table.

1. below is my test table

Table:

Year:

2. create ameasure with below dax formula

Measure =
VAR _year =
    SELECTEDVALUE ( 'Year'[Year] )
VAR _date =
    DATE ( _year, 12, 31 )
VAR lu =
    SELECTEDVALUE ( 'Table'[Last Update] )
RETURN
    IF ( ISFILTERED ( 'Year'[Year] ), ABS ( DATEDIFF ( lu, _date, DAY ) ), "" )

3. add a slicer with Year field, add a table visual with Table fields and measure

 

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[barritown · Accepted solution]

Hi @ajisharavind_99,

Let's assume you have such a calendar table not connected to the main one:

Having that table, you could create such a measure:

In plain text for convenience:

Diff Measure = 
VAR LastUpdate = MIN ( [Last Update] )
VAR MaxDate = MAX ( 'Date'[Date] )
RETURN DATEDIFF ( LastUpdate, MaxDate, DAY ) 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[ajisharavind_99]

@barritown @v-binbinyu-msft I appreciate your prompt reply. You made my day, and both were successful.