Need Unique Date Time Field For Moving Average Chart

[cbruhn42]

I have a data set that looks like this:

Date / Time    -    Production Order (Unique Value)   -    Brand    -    Phase   -    Value   -    Index   -     Moving Range

I sorted the data by phase, brand and production order all ascending and then added an index column so that I can reference the index column when calculating my moving range.

My issue is that in my data set the Date / Time column is the date at midnight of that day.  It just defaults to that and is how it's setup in the database. 

So when I go to chart the data I sum the value based on the phase and brand.  If there happens to be two rows with the same date, then those values get summed together.  If I add the production order under the date / time field on the x-axis then my average and process limit lines get all wonky.

Any thoughts on how I can fix this?  One thing I was thinking is that if I could create a custom column that checks for dupliate date / times and then adds like 1 second to the subsequent date / times so I get a unique time.  In our process we could never start more than 2-3 tanks of the same brand on the same day due to process limitations.

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from amitchandak.

Hi @cbruhn42  , 

For you in the last paragraph of the problem description, I created a simple example data and realized the vision that you can apply that date column to the visual according to your own reality, here are my steps:

1.Create simple data:

2.Create a calculated column:

UniqueDateTime =
VAR _CurrentDate = 'Table'[Date / Time]
VAR _RowNumber = 'Table'[Index]
VAR _DuplicateCount =
COUNTROWS(
FILTER(
'Table',
'Table'[Date / Time] = _CurrentDate &&
'Table'[Index] < _RowNumber
)
)
RETURN
IF(_DuplicateCount > 0,
_CurrentDate + TIME( 0,0,_DuplicateCount),
_CurrentDate)

3.This achieves the effect of adding 1 to the number of seconds with the same time. 

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@cbruhn42 , Can you try new visual calculation for Moving Avg

Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

You can also consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f