Forum Discussion

tharaujo85's avatar
tharaujo85
Frequent Visitor
2 years ago
Solved

Retrieve First Date with a measure condition

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 meas...
"Time"
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
tharaujo85
Frequent Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tharaujo85
      Frequent Visitor
      2 years ago

      Thanks Anonymous ! It worked just fine!