Forum Discussion
Apply a filter to remove a specific date for DST
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 |
Hi Anonymous ,
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.
3 Replies
- Thingsclump
Resolver V
Hey Anonymous
Can you please provide the input data as a table please. I belive the table that you have shown is result table.
Thanks
Thingsclump
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.
Hi Anonymous ,
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.
