SUM data from the selected date and the date before the selected date

[andersonrickard]

Hello,

When I choose a date i want to SUM values in a column for that date and for the day before the selected date.

In the same table as the date filter I also have a DATETIME column which shows every hour of the day. 

I also need to filter so I only get data from after 22:00 from the day before and before 06:00 the day after. That means if I choose 03.07.2021 I want to SUM data from the period 02.07.2021 22:00 - 03.07.2021 06:00.

The date filter is from a table called "DateTime" and the column is called "Date" (DateTime[Date])

The DATETIME column is also in the table "Datetime" (DateTime[DateTime])

The column I want to SUM is from a table called "OM44a" and the column is named "# Packed *" (OM44a[# Packed *])

In the Date table I also have a column for TIME and column for HOUR.

Thanks in advance

Rickard

[andersonrickard · Accepted solution]

@Greg_Deckler I found a workaround that works. It isn't pretty.

I did a new column showing only TIME then I did multiple measures to that table and made them work in the formula you gave me.

Antall DSP2 = CALCULATE(SUM(OM44a[# Packed *]);OM44a[Time] < TIMEVALUE("06:00"))
Antall DSP3 = CALCULATE(SUM(OM44a[# Packed *]);OM44a[Time] >= TIMEVALUE("22:00"))

Measure 5 = 
  VAR __SelectedDate = MAX(DateTime[Date])
  VAR __Previous = __SelectedDate - 1
RETURN
  SUMX(FILTER(ALL(DateTime);DateTime[Date] = __SelectedDate); [Antall DSP2])+SUMX(FILTER(ALL(DateTime);DateTime[Date] = __Previous); [Antall DSP3])

[andersonrickard]

@Greg_Deckler Thank you for your answer. I also want to thank you for all your answers in a lot of other threads. You helped me a lot since i started with Power BI.

Your formula worked to give me the SUM from the selected date and the day before

Measure 4 = 
  VAR __SelectedDate = MAX(DateTime[Date])
  VAR __Previous = __SelectedDate - 1
RETURN
  SUMX(FILTER(ALL(DateTime);DateTime[Date] >= __Previous && DateTime[Date] <= __SelectedDate); [Antall DSP])

 
Now I need to get the formula to only show values from before 06:00 on the selected date and after 22:00 the day before. Is this possible? Any ideas?  

Thanks 

Rickard

[Greg_Deckler]

@andersonrickard A lot to unpack here and hard to exactly visualize without sample data. With your first request, it would be along the lines of:

Measure =
  VAR __SelectedDate = MAX('Table'[Date])
  VAR __Previous = __SelectedDate - 1
RETURN
  SUMX(FILTER(ALL('Table'),[Date] >= __Previous && [Date] <= __SelectedDate), [# Packed *])