Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX Issue on Product Recertification

Hello, 

I have an issue. I want to show KRI like this one: 

I have one table with column: Product and Yearly Review: 

Product NameYearly review
Product 1Thursday, 24 November 2022
Product 2Sunday, 21 May 2023
Product 3Saturday, 3 June 2023
Product 4Friday, 22 September 2023
Product 5Wednesday, 20 September 2023
Product 6Tuesday, 22 June 2021
Product 7Saturday, 17 July 2021
Product 8Saturday, 29 October 2022
Product 9Saturday, 8 July 2023
Product 10Thursday, 25 March 2021
Product 11Saturday, 12 June 2021
Product 12Saturday, 24 June 2023
Product 13Saturday, 8 May 2021
Product 14Wednesday, 10 February 2021
Product 15Thursday, 8 June 2023
Product 16Friday, 16 December 2022
Product 17Saturday, 29 October 2022
Product 18Saturday, 5 February 2022
Product 19Saturday, 12 March 2022

1) So I created the calculated column: 

Max Date =
VAR _current_name = 'Table'[Product_Name]
VAR _p_table =
    FILTER ( 'Table', 'Table'[Product_Name] = _current_name )
RETURN
    MAXX ( _p_table, [Yearly_Review] )

2) I created two measures regarding objectives: 

Less than 6 months =
VAR _table =
    SUMMARIZE ( 'Table', 'Table'[Product_Name], 'Table'[Max Date] )
VAR _filter =
    FILTER (
        _table,
        DATEDIFF ( [Max Date], TODAY (), MONTH ) <= 6
            && DATEDIFF ( [Max Date], TODAY (), MONTH ) >= 0
            && [Max Date] < TODAY ()
    )
RETURN
    COUNTROWS ( _filter )
More than 6 months =
VAR _table =
    SUMMARIZE ( 'Table', 'Table'[Product_Name], 'Table'[Max Date] )
VAR _filter =
    FILTER (
        _table,
        [Max Date] < TODAY ()
            && DATEDIFF ( [Max Date], TODAY (), MONTH ) > 6
    )
RETURN
    COUNTROWS ( _filter )

3) I want to show this value on the two visual: card and table like this one: 

So I create calculated column "recrertified column" 

Recertificated Column =
IF('Table'[Less than 6 months] = 1, "Less than 6 Months",
IF('Table'[More than 6 months] = 1, "More than month"))

 

but I don't know how calculate the Green objectives: "All Product recertification means it has been recertified between today and the last 6 months". Could you check and help me with this?

 



Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Maybe:

     

    Green =
VAR _table =
    SUMMARIZE ( 'Table', 'Table'[Product_Name], 'Table'[Max Date] )
VAR _filter =
    FILTER (
        _table,
        [Max Date] > EOMONTH(TODAY(),-6)
    )
RETURN
    COUNTROWS ( _filter )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello Greg_Deckler , thanks a lot for this solution, but why do we have such a high value in Green, what is counted by the measure?

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      Anonymous Well, it is counting the 11 rows in your image that currently have a blank value for Recertification Column because presumably they don't meet the other two criteria so they are Green?