Help with conditional formulas.

[EduardoOrtega]

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.

[v-rongtiep-msft]

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]

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.

[v-rongtiep-msft]

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.