Forum Discussion

Sudharshan1919's avatar
Sudharshan1919
Icon for Helper III rankHelper III
4 years ago
Solved

Latest Date

Hi All,

 

I am having 3 region sheets like Region1, Region2 and Region3. Those files get updated on a daily basis 4-5 times per day including with the timestamp, where the data is stored in the "DateTime Column" in that table. As of now i am displaying the data with the latest date time field. 

Below is the example how the data gets updated.

Region 1 : 14-09-2022 02:05:00 PM

Region 2:  14-09-2022 02:08:00 PM

Region 3:  14-09-2022 02:14:00 PM

Below is the Calculated  Column using DAX that we are using for each region seperately

Latest Date =
VAR CurrentDate = 'Region1'[Date Time - Region1]
VAR CurrentServer = 'Region1'[ServerName]
VAR EachServerTable =
     FILTER(
         'Region1',
         'Region1'[ServerName] = CurrentServer
     )
VAR LastDateTimeFetched =
   MAXX(
       'Region1',
       'Region1'[Date Time - Region1]
   )
RETURN
 IF(
     CurrentDate = LastDateTimeFetched,
     "Yes"
 )
Now, all these 3 sheets were merged and we have modified the DAX  a bit, but the latest data is displaying only for the latest available data (Consider in our example as Region3), but i need the latest data to be under the latest updated data for each region.
Can someone help me on this ?
 
Thanks
  • johnt75's avatar
    johnt75
    4 years ago

    Something like this should work as a calculated column

    Latest date =
VAR CurrentDate = 'Table'[Date time]
VAR MaxDate =
    CALCULATE ( MAX ( 'Table'[Date time] ), ALLEXCEPT ( 'Table', 'Table'[Region] ) )
RETURN
    IF ( CurrentDate = MaxDate, "Yes" )

1 Reply

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

    Something like this should work as a calculated column

    Latest date =
VAR CurrentDate = 'Table'[Date time]
VAR MaxDate =
    CALCULATE ( MAX ( 'Table'[Date time] ), ALLEXCEPT ( 'Table', 'Table'[Region] ) )
RETURN
    IF ( CurrentDate = MaxDate, "Yes" )