topic SUM data from the selected date and the date before the selected date in DAX Commands and Tips

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

andersonrickard — Wed, 04 Aug 2021 13:04:23 GMT

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

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

Greg_Deckler — Wed, 04 Aug 2021 13:22:55 GMT

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 *])

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

andersonrickard — Thu, 05 Aug 2021 06:41:26 GMT

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

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

andersonrickard — Thu, 05 Aug 2021 08:56:08 GMT

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])