Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
2 years ago
Solved

Max Completed Date

Hi Good Day,

I need help on my table, pls refer below. i want only the maximun date of completed group category to register on my Week Column and year column, on my example only 3 group category completed the job. My table connected to my dimdate table.

 

 

Thank you

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from rajendraongole1 , please allow me to provide another insight: 

    Hi  AllanBerces ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Week No. =
var _count100=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Comp %]=100),[Comp %])
var _count=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Comp %])
var _alldate=MAXX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Date Completed])
return
IF(
    _count100=_count&&'Table'[Date Completed]=_alldate,WEEKNUM(_alldate,2),BLANK())
    Year =
var _count100=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Comp %]=100),[Comp %])
var _count=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Comp %])
var _alldate=MAXX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Date Completed])
return
IF(
    _count100=_count&&'Table'[Date Completed]=_alldate,YEAR(_alldate),BLANK())

    2. Result:

    As far as I can see from looking at your sample data, I found a bit of information that is a little different from what you said:

    EEE max date should be 2024.5.11

    HHH max date should be 2024.4.17

     

    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

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi AllanBerces -Create a measure that calculates the maximum completion date for each group category.

     

    MaxCompletionDate =
    CALCULATE(
        MAX('Category'[Date Completed]),
        FILTER(
            'Category',
            'Category'[Comp%] = 100
        )
    )
     
    Calculated Columns for Week and Year:
     
    CompletionWeek =
    WEEKNUM('Category'[Date Completed])
     
    CompletionYear =
    YEAR('Category'[Date Completed])
     

     

    Create another measure for true or false condition IsMaxCompletionDateMeasure as a Filter to show only rows where IsMaxCompletionDate is 1.

    IsMaxCompletionDate =
    IF(
    'Table'[Date Completed] = [MaxCompletionDate] && 'Table'[Comp%] = 100,
    1,
    0
    )

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      2 years ago

      Hi rajendraongole1 thank you for the reply, but i need to reflect only the completed category group max date. which is on the below.

      Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from rajendraongole1 , please allow me to provide another insight: 

    Hi  AllanBerces ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Week No. =
var _count100=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Comp %]=100),[Comp %])
var _count=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Comp %])
var _alldate=MAXX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Date Completed])
return
IF(
    _count100=_count&&'Table'[Date Completed]=_alldate,WEEKNUM(_alldate,2),BLANK())
    Year =
var _count100=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Comp %]=100),[Comp %])
var _count=COUNTX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Comp %])
var _alldate=MAXX(FILTER(ALL('Table'),'Table'[Category]=EARLIER('Table'[Category])),[Date Completed])
return
IF(
    _count100=_count&&'Table'[Date Completed]=_alldate,YEAR(_alldate),BLANK())

    2. Result:

    As far as I can see from looking at your sample data, I found a bit of information that is a little different from what you said:

    EEE max date should be 2024.5.11

    HHH max date should be 2024.4.17

     

    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

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      2 years ago

      Hi Anonymous thank you very much work as i need.