Forum Discussion

vivek_babu's avatar
vivek_babu
Helper II
1 year ago
Solved

DAX Calculated Column Issue

Hi All,   I am trying to calculate the previous average coverage percentage based on the reporting date and system name columns. The formula i used below works if i dont consider system name to get...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi vivek_babu 

    Please try the follow dax:

    Previous_Percentage_Coverage = 
var currentdate = DEEP_SECURITY[REPORTING_DATE]
var previousdate = MAXX(FILTER(ALL(DEEP_SECURITY),DEEP_SECURITY[REPORTING_DATE] < currentdate),DEEP_SECURITY[REPORTING_DATE])
var currentsysname = max(DEEP_SECURITY[SYSTEM_NAME])
RETURN
MAXX(FILTER(ALL(DEEP_SECURITY),DEEP_SECURITY[REPORTING_DATE] = previousdate && DEEP_SECURITY[SYSTEM_NAME] = EARLIER(DEEP_SECURITY[SYSTEM_NAME])
),
DEEP_SECURITY[avg_percentage_covered])

     

     

    Result:

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

FreemanZ's avatar
FreemanZ
Super User
1 year ago

hi vivek_babu ,

 

try like:

column =

VAR _name = [NAME]

VAR _date = [DATE]

RETURN

MAXX(

    TOPN(

       1,

       FILTER(

           DEEP, 

           DEEP[DATE]<_date

              &&DEEP[NAME]=_name

       ),

       DEEP[DATE]

   ),

   DEEP[avg]

)

  • vivek_babu's avatar
    vivek_babu
    Helper II
    1 year ago

    Hi FreemanZ 

     

    Thanks for providing solution. Unfortunately, its not giving me the desired outcome. I am getting blanks and incorrect values so please check below,

     

    Formula:

    Prev_Perc =
    var _name = DEEP_SECURITY[SYSTEM_NAME]
    var _date = DEEP_SECURITY[REPORTING_DATE]
    RETURN
    MAXX(
        TOPN(
            1,FILTER(DEEP_SECURITY,DEEP_SECURITY[REPORTING_DATE] < _date && DEEP_SECURITY[SYSTEM_NAME] < _name
            ),
            DEEP_SECURITY[REPORTING_DATE]
        ),
        DEEP_SECURITY[avg_percentage_covered]
    )
     
    Screenshot:


    Regards

    Vivek N