Retrieve First Date with a measure condition

[tharaujo85]

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.

[Anonymous]

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

[tharaujo85]

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

)

[Anonymous · Accepted solution]

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

[tharaujo85]

Thanks @Anonymous ! It worked just fine!