topic Re: Retrieve First Date with a measure condition in DAX Commands and Tips

Retrieve First Date with a measure condition

tharaujo85 — Fri, 13 Oct 2023 19:33:59 GMT

Hi,

This is my first post, so I apologize in advance if I'm not 100% clear.

I have a measure that indicates the coverage in months of my inventory. I want to retrieve the first date which that measure is under 3 months. In this scenario it would be 11/03/2024 for all days. The problem is that I'm not working well with the time filters, that influence the PrevEstq measure.

Re: Retrieve First Date with a measure condition

Anonymous — Mon, 16 Oct 2023 07:22:39 GMT

Hi tharaujo85 ,

According to your description, it seems that you want to get the first date which fulfill the specific conditions. Base on provided data, why the returned result is 11/03/2024? Could you please provide some raw data in your tables (exclude sensitive data) with Text format, the formula of measure [PrevEstq] and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Retrieve First Date with a measure condition

tharaujo85 — Mon, 16 Oct 2023 15:14:36 GMT

Hi, Anonymous

I will try to build a model, but it is based on a sql server from the company so it will take some time.

In the mean time, to explain why the result is 11/03/2024, it is because it is the first time when [PrevEstq] is under 3.

My PrevEstq measure is the following:

VAR CMMHoje = CALCULATE( [CMM], DIM_TEMPO[dat_data] = TODAY() )

VAR Estq = SUM ( FAT_ESTOQUE[Qtd_UL_Estoque] ) + SUM ( FAT_ESTOQUE[Qtd_Control_Qualidade] )

VAR CobPed =

CALCULATE(

DIVIDE( SUM( FAT_PEDIDOS[Qtd_Saldo] ), CMMHoje ),

DIM_TEMPO[dat_data] <= MAX(DIM_TEMPO[dat_data] )

)

VAR CobEstq = DIVIDE ( Estq , CMMHoje )

VAR CobConsumoDia =

DIVIDE(

DATEDIFF(MAX(DIM_TEMPO[dat_data]),TODAY(),DAY),

365/12

)

VAR PrevCobAtual = CobEstq + CobPed + CobConsumoDia

RETURN

IF (

MIN (DIM_TEMPO[dat_data]) >= TODAY(),

PrevCobAtual

)

Re: Retrieve First Date with a measure condition

Anonymous — Tue, 17 Oct 2023 05:27:02 GMT

Hi tharaujo85 ,

Thanks for your reply. You can create a measure as below to get it, please check if it can return the expected result...

Measure = MINX ( FILTER ( ALLSELECTED ( DIM_TEMPO ), [PrevEstq] < 3 ), DIM_TEMPO[dat_data] )

If the above one can't help you get the expected result, you can create a simplified pbix file with fake data. Then share it with us as referred in my previous post.

How to upload PBI in Community

Best Regards

Re: Retrieve First Date with a measure condition

tharaujo85 — Tue, 17 Oct 2023 11:24:40 GMT

Thanks Anonymous ! It worked just fine!