Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need help with the DAX Calculation

Hi Team,

 

I have a sample file. (I'm unable to attach the file over here but I can share)

 

Based on the data, the output generated by the below dax for "Average Lead Time" is 16.

 

Average Lead Time =
CALCULATE(MAX(Sheet2[LEAD_TIME_NR]),
FILTER(ALLSELECTED(Sheet2[LEAD_TIME_NR]),[1 % Of Arrivals Running Total] >= 0.8 && [1 Previous Cumulative] < 0.8)
)

 

Other Dax used:

 

1 % Of Arrivals Running Total = 
CALCULATE(
[1 % Of Arrivals],
FILTER(
ALLSELECTED('Sheet2'[LEAD_TIME_NR]),
ISONORAFTER('Sheet2'[LEAD_TIME_NR], MAX('Sheet2'[LEAD_TIME_NR]), DESC)
)
)
 
 
1 Previous Cumulative = [1 % Of Arrivals Running Total] - [1 % Of Arrivals] 
 
 
1 % Of Arrivals = divide([Stay Number of Arrivals], [1 Total Arrivals])
 
 
Stay Number of Arrivals:= CALCULATE(sum(Sheet2[ROOMS_OCC_NR]), FILTER(Sheet2, Sheet2[ARRIVAL_IND] = "Y"))
 
 
1 Total Arrivals = CALCULATE(sum(Sheet2[ROOMS_OCC_NR]), all(Sheet2[LEAD_TIME_NR]), Sheet2[ARRIVAL_IND] = "Y")
 
 

I tried but unable to break the logic in the dax. Please if someone could help me would be greatly appreciated.

 

Many Thanks,

Mithilesh

 

 

"DAX "
"DAX help" DAX
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lbendlin,

     

    I was able to break down the working of DAX. Basically, it is performing the running totals to get a distribution of lead time with respect to the average lead time. And this is done by the function ISONORAFTER

9 Replies