Need help with the DAX Calculation

[mith123]

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

[mith123 · Accepted solution]

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[mith123]

Hi @lbendlin,

Thank you so much for responding.

I have uploaded the file to the following link.

https://www.dropbox.com/scl/fi/mk1rtyuj5vfi58renianc/Sample-File.xlsx?rlkey=9jfalqlo4ae90urn704ocf7u...

Thanks,

Mithilesh

[lbendlin]

1 % Of Arrivals = divide([Stay Number of Arrivals], [1 Total Arrivals])

This makes no sense as it will always return 1

What are you trying to achieve ultimately?

[mith123]

Hi @lbendlin,

You're absolutely right, it will always give 1. Actually, this was written by someone a long time back.

I would like to trace how the "Average Lead Time" is 16

Please help me.

Thanks,

Mithilesh

[lbendlin]

"Average Lead Time" is neither an average nor will it ever hit 16, at least not based on your sample data. 

What is the business problem you are trying to solve?

[mith123]

I'm trying to find the "Average Lead Time". As per the Power BI file, I created, it shows ""Average Lead Time" as 16. The Power BI file is in the following location

https://www.dropbox.com/scl/fi/mk1rtyuj5vfi58renianc/Sample-File.xlsx?rlkey=9jfalqlo4ae90urn704ocf7u...

Thanks,

Mithilesh

[lbendlin]

Have you tried a graphical solution?  Here is the average lead time based on your raw data, by date, Arrival Indiator and Rooms Occ Nr.

What is the point of the running totals?

[mith123]

Yes, I did and I'm getting 13.88

However, I'm curious to know how Power BI is returning 16 based on the DAX formula.

I think I'm missing something in the following DAX:

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)

)

)

Thanks,

Mithilesh