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 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.

"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

4 Replies

  • 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