topic Re: Text and hour into a single column in DAX Commands and Tips

Text and hour into a single column

Anonymous — Thu, 12 Oct 2023 07:08:48 GMT

hello, i am trying to create a table with a column as above where the rollover will be the first row above the hour. i have tried using IF functions but the result is not the same. the image is showing the hour of the date and i am assuming the rollover are those orders before the current date.

i can definitely create different tables to show the difference, but the desired outcome from the end user is as above snippet.

Re: Text and hour into a single column

123abc — Thu, 12 Oct 2023 11:32:21 GMT

You can use the following DAX formula as an example:

RolloverColumn =
VAR CurrentHour = HOUR(YourTable[YourDateColumn])
RETURN
CALCULATE(
MIN(YourTable[YourDateColumn]),
FILTER(YourTable, HOUR(YourTable[YourDateColumn]) < CurrentHour)
)

Replace "YourTable" with the name of your table and "YourDateColumn" with the name of the column that contains the dates or timestamps. This formula calculates the minimum date/time value that occurs before the current hour in your table.

Add the new calculated column to your table visualization in the report view.
Format the table as needed to achieve the desired appearance. You can modify the column headers, formatting, and other visual aspects to make it look like the snippet you provided.

This approach will create a table in Power BI where the "RolloverColumn" contains the first row above the hour, just as you described in your snippet. Adjust the column names and formatting as necessary to match your specific requirements.

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

Re: Text and hour into a single column

Anonymous — Mon, 16 Oct 2023 03:24:20 GMT

Hi Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Hour = FORMAT( 'Table'[Date],"hh")

Text = [Hour] &":"&"00"

2. Create calculated table.

Table 2 = var _table1= {"Rollover"} var _table2= UNION(_table1,DISTINCT('Table'[Text])) return ADDCOLUMNS( _table2,"Rcvd QTY", IF( [Value]="Rollover",SUMX(ALL('Table'),[Value]), SUMX(FILTER(ALL('Table'),'Table'[Text]=EARLIER([Value])),[Value])))

3. Result:

Best Regards,

Liu Yang

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

Re: Text and hour into a single column

Anonymous — Mon, 16 Oct 2023 05:39:52 GMT

hello Sir,

i have tried the solution you provided but it returns a different outcome to what i am looking for, nonetheless good Sir, thank you for your time on this query.