Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
@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])
@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
@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 *])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |