Forum Discussion

EduardoOrtega's avatar
EduardoOrtega
Regular Visitor
3 years ago

Help with conditional formulas.

Good day,

I am new to using Power BI and I request your valuable help. I have the following set of data coming from a folder with very heavy Excel files:

 

What I need is a formula or some way to help me determine, depending on the day (conciliation date column), which transactions were made on each day from March 1st to 31st based on the txn date column (which has the format "dd/mm/yyyy hh:mm:ss am, pm"), and classify them into 2 parameters: "Same period" and "Other period".

For "Same period", all transactions made from February 27th after 23:00:00 onwards, counting everything that is in March will be included. For "Other period", they will be those that are outside the previous range, whether before February 27th at 22:59:59 backwards, including other months and previous years.

I hope I have been able to explain the need well and that you can help me. Thank you very much.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi EduardoOrtega ,

    I have created as simple sample, please refer to it to see if it helps you.

    Create 2 columns.

    max_time = FORMAT( dt"2023-3-31T23:59:59", BLANK(), "en-GB" )
    min_time = FORMAT( dt"2023-2-27T23:00:00", BLANK(), "en-GB" )

    Then create a measure.

    Measure =
IF (
    MAX ( 'Table'[datetime] ) >= SELECTEDVALUE ( 'Table'[min_time] )
        && MAX ( 'Table'[datetime] ) <= SELECTEDVALUE ( 'Table'[max_time] ),
    "Same period",
    "Other period"
)

    Or still use column.

    column =
IF (
     ( 'Table'[datetime] ) >= earlier ( 'Table'[min_time] )
        &&  ( 'Table'[datetime] ) <= earlier ( 'Table'[max_time] ),
    "Same period",
    "Other period"
)

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • EduardoOrtega's avatar
      EduardoOrtega
      Regular Visitor
      3 years ago

      First of all, thank you very much for your support, but I don't know how to use what you replied with my data or how to get the result. What you provided is very helpful, it's exactly what I want, I just don't know how to add it to my data. Beacuse i do not have those two tables min & max.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi EduardoOrtega ,

        You can create 2 columns.I used these two columns to fix the time.

        Then change the result column.

        Column = IF (
     ( 'Table'[datetime] ) >=  ( 'Table'[min_time] )
        &&  ( 'Table'[datetime] ) <=  ( 'Table'[max_time] ),
    "Same period",
    "Other period"
)

        How to Get Your Question Answered Quickly 

         

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

         

        Best Regards
        Community Support Team _ Rongtie

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.