Apply a filter to remove a specific date for DST

[NZCraig]

Hi,

I have a table that shows days divided into 30 min segments, with a production total for each period and when there is a Daylight Saving day, there will be 50 or 46 segments, not 48. I need to be able to display the best production day, but exclude days that have 50 segments. Example table below - not showing all periods 🙂 I need to be able to show the total for days including DST so can't just filter out the days where DST occurs.

I have referenced the source table and grouped by the date column to find the day with the highest value, but I am stuck in trying to find a way to filter out an entire day so that I can take the best day, ignoring DST changes. Any advice would be appreciated!

DATE YYYYMMDD	PERIOD	OUTPUT
20210520	46	321.1
20210520	47	320.0
20210520	48	319.6
...	...	...
20210521	46	322.1
20210521	47	321.5
20210521	48	320.2
20210521	49	318.4
20210521	50	320.5
...	...	...
20210522	46	312.2
20210522	47	319.0
20210522	48	319.9

[Anonymous · Accepted solution]

Hi @NZCraig ,

According to the table you provided, I first created a date column.

Date = Date(Left([DATE YYYYMMDD],4),Right(left([DATE YYYYMMDD],6),2),right([DATE YYYYMMDD],2))

Then create two measures. The first meaesure is to calculates the sum of the output grouped by date.

Sum = CALCULATE(SUM('Table'[OUTPUT]),FILTER(ALLSELECTED('Table'),[DATE YYYYMMDD]=MAX('Table'[DATE YYYYMMDD])))

the best production day = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Sum]= MAXX(ALL('Table'),[Sum])))

Best Regards,

Stephen Tao

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

[Thingsclump]

Hey @NZCraig 

Can you please provide the input data as a table please. I belive the table that you have shown is result table.

Thanks

Thingsclump

www.thingsclump.com

[NZCraig]

Hi @Thingsclump . I can't provide the actual table, commercial stuff and all, but the data looks like as shown. with  one extra columns. A date column (not formated as a date data type), a period column showing 1- 48 for most days, except daylight saving days, and a output column showing the production output as received from SCADA for each period.

I think I might have worked out a way aournd it, by duplicating the table, grouping by date, aggregating SUM of output data, and MAX period number. I'm then filtering out the days where MAX period = 50.