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 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 Anonymous lbendlin amitchandak rajendraongole1 danextian M_Chris FreemanZ Ritaf1983 johnt75 FarhanJeelani shafiz_p

Regards

Vivek N

Need Help

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.

DAX Calculated Column Issue.pbix41 KB

7 Replies

Anonymous
Not applicable
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.
DAX Calculated Column Issue.pbix41 KB
- vivek_babu
  Helper II
  1 year ago
  Hi Anonymous
  
  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
vivek280393
New Member
1 year ago
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 Anonymous lbendlin amitchandak rajendraongole1 Ritaf1983 danextian shafiz_p FreemanZ johnt75

Regards
Vivek N
saud968
Memorable Member
1 year ago
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!
vivek_babu
Helper II
1 year ago
Hi saud968

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

Screenshot:

lbendlin vojtechsima Anonymous amitchandak rajendraongole1 danextian Ritaf1983 johnt75 shafiz_p FreemanZ

Regards
Vivek N
- 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
    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

Forum Discussion

DAX Calculated Column Issue

7 Replies

Related content

TMDL - calculated column

Matrix calculation Issue - DAX

Direct Lake Model - Calculated Column/Tables

DAX Calculation Issues

MaxDate Issue Calculated Column

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