How to calculate Max-min for each day from a large dataset

[Naveen59]

Hi Team, 

I am new at power BI, I want to calculate Max-Min of each day and if there will be Zero then we have to ignore Zero and need to substract from values only, here is the sample data, please help me for this on urgent basis.

Date	Values
8/23/202210:10	57384.29
8/23/2022 9:40	228375.3
8/23/2022 8:10	2529279
8/23/2022 7:50	224606.7
8/23/2022 3:10	2529154
8/23/2022 2:50	1273840
8/23/2022 2:30	1273826
8/23/2022 1:40	2529154
8/23/2022 1:00	2529154
8/23/2022 0:40	534695.1
8/22/2022 20:20	2529154
8/22/2022 20:00	1273508
8/22/2022 11:50	2528039
8/22/2022 11:30	76444.75
8/22/2022 10:10	2527662
8/22/2022 9:50	208444.8
8/22/2022 9:20	228125
8/22/2022 7:20	214431.5
8/22/2022 5:30	2526704
8/22/2022 5:10	57302.12
8/22/2022 3:50	1272105
8/22/2022 1:40	2526514
8/22/2022 1:20	57283.45
8/22/2022 0:10	2526442
8/21/2022 22:50	2526376
8/21/2022 22:40	1272097
8/21/2022 22:30	496331.1
8/21/2022 21:30	2526311
8/21/2022 21:10	496294.9
8/21/2022 17:20	2526105
8/21/2022 17:00	57242.8
8/21/2022 16:20	2526054
8/21/2022 16:00	496154.5
8/21/2022 13:10	2525844

[v-binbinyu-msft · Accepted solution]

Hi @Naveen59 ,

Please try below steps:

1. create a new column with below dax formula

Date_v2 =
VAR _val = LEFT ( [Date], 9 ) RETURN DATEVALUE ( _val )

2. create two measure with below dax formula

Max Value =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date_v2] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Date_v2] = cur_date )
RETURN
    MAXX ( tmp, [Values] )

Min Value =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date_v2] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Date_v2] = cur_date )
RETURN
    MINX ( tmp, [Values] )

3. add a table visual with new column and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Naveen59]

Hi amit, 

I have already tried this step but getting right value.

[amitchandak]

@Naveen59 , Create a date column in power query or bring it from source

date 1=  DateTime.Date([Date])

Use this in visual and take min/max of value as measures

Min val = min(Table[Values])

Max val = max(Table[Values])