DAX Calculated Column Issue

[vivek_babu]

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 the previous average coverage for the previous reporting dates. But, i want to get the previous average coverage percentage for each system name and its previous reporting dates. 

Screenprint:

Actual output i am expecting,

Formula:

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])

var previoussysname = MAXX(FILTER(ALL(DEEP_SECURITY),DEEP_SECURITY[SYSTEM_NAME] <> currentsysname),DEEP_SECURITY[SYSTEM_NAME])

RETURN

MAXX(FILTER(ALL(DEEP_SECURITY),DEEP_SECURITY[REPORTING_DATE] = previousdate && DEEP_SECURITY[SYSTEM_NAME] <> previoussysname

),

DEEP_SECURITY[avg_percentage_covered])

Please check and let me know how to correct the formula to get the desired result 

@vojtechsima @v-xianjtan-msft @lbendlin @amitchandak @rajendraongole1 @danextian @M_Chris @FreemanZ @Ritaf1983 @johnt75 @FarhanJeelani @shafiz_p 

Regards

Vivek N

[v-jialongy-msft · Accepted solution]

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.

[vivek_babu]

Hi @v-jialongy-msft 

Thank you for providing the solution!  It worked and i am getting the desired outcome now!

Please check below,

I appreciate your help and thanks once again 🙂

Regards

Vivek N

[vivek_babu]

Hi @saud968 

Thanks for providing your solution. I tried your solution but i am getting blanks as output,

Screenshot:

@lbendlin @vojtechsima @v-xianjtan-msft  @amitchandak @rajendraongole1 @danextian @Ritaf1983 @johnt75 @shafiz_p @FreemanZ 

Regards

Vivek N

[FreemanZ]

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]

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

[vivek280393]

Hi All,

I am trying to calculate the previous average percentage column based on reporting date and system name columns. 

The formula is working when i use reporting date alone to get the previous average percentage but i have to consider the system name so the average needs to be done for each system name and then calcualte the previous reporting date but i am not getting the desired result. 

Screenshot:

Formula used:

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])

var previoussysname = MAXX(FILTER(ALL(DEEP_SECURITY),DEEP_SECURITY[SYSTEM_NAME] <> currentsysname),DEEP_SECURITY[SYSTEM_NAME])

RETURN

MAXX(FILTER(ALL(DEEP_SECURITY),DEEP_SECURITY[REPORTING_DATE] = previousdate && DEEP_SECURITY[SYSTEM_NAME] <> previoussysname

),

DEEP_SECURITY[avg_percentage_covered])

Actual output i am expecting is below,

Percentage Coverage	Reporting Date	System Name	Previous Percentage Coverage
85.29	7/1/2018	DS AWS 2.0
95.5	7/1/2018	DS AWS 1.0
90.48	8/1/2018	DS AWS 2.0	85.29
92.86	8/1/2018	DS AWS 1.0	95.5

Please check and let me know where is the issue

@vojtechsima @v-xianjtan-msft @lbendlin @amitchandak @rajendraongole1 @Ritaf1983 @danextian @shafiz_p @FreemanZ @johnt75 

Regards

Vivek N

[saud968]

Try this Dax 

Previous_Percentage_Coverage =
VAR currentdate = DEEP_SECURITY[REPORTING_DATE]
VAR currentsysname = DEEP_SECURITY[SYSTEM_NAME]
VAR previousdate = MAXX(
FILTER(
ALL(DEEP_SECURITY),
DEEP_SECURITY[REPORTING_DATE] < currentdate &&
DEEP_SECURITY[SYSTEM_NAME] = currentsysname
),
DEEP_SECURITY[REPORTING_DATE]
)
RETURN
CALCULATE(
MAX(DEEP_SECURITY[avg_percentage_covered]),
DEEP_SECURITY[REPORTING_DATE] = previousdate,
DEEP_SECURITY[SYSTEM_NAME] = currentsysname
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!