topic Count Number of Days from Selected year to Last update in DAX Commands and Tips

Count Number of Days from Selected year to Last update

ajisharavind_99 — Mon, 24 Jul 2023 08:43:45 GMT

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

Re: Count Number of Days from Selected year to Last update

barritown — Mon, 24 Jul 2023 18:35:40 GMT

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

Re: Count Number of Days from Selected year to Last update

Anonymous — Wed, 26 Jul 2023 03:12:37 GMT

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.

Re: Count Number of Days from Selected year to Last update

ajisharavind_99 — Wed, 26 Jul 2023 03:37:33 GMT

barritown Anonymous I appreciate your prompt reply. You made my day, and both were successful.