Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
schwibach
Frequent Visitor

Calculate sum of column for each date, table contains begin date and end date

‎02-12-2022 12:59 AM

Hello,

 

I want to do a forecast of how many (boarding school) rooms we will need at any given date in the next year. 

I have prepared a table with three columns: a begin date, an end date and the number of people that will be in that group.

 

I would like to show the maximum value (most people present at the same time) and bar charts that will show the number of people present at that time. 

Labels:
Message 1 of 7
1,740 Views
0
1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

‎02-17-2022 12:02 AM

Hi @schwibach ,

Test the below column:

anzahl1 = sumx(filter(dev_Blockklassen,dev_Blockklassen[Datum]=EARLIER(dev_Blockklassen[Datum])),dev_Blockklassen[Anzahl])

 

Output result:

vluwangmsft_0-1645084880714.png

 

 If not correct ,could you pls show me your expect correct output?

 

 

Best Regards

Lucien

View solution in original post

Calculate sum of column for each date, table contains begin date and end date.pbix
Message 7 of 7
1,626 Views
0
6 REPLIES 6
v-luwang-msft
Community Support
Community Support

‎02-17-2022 12:02 AM

Hi @schwibach ,

Test the below column:

anzahl1 = sumx(filter(dev_Blockklassen,dev_Blockklassen[Datum]=EARLIER(dev_Blockklassen[Datum])),dev_Blockklassen[Anzahl])

 

Output result:

vluwangmsft_0-1645084880714.png

 

 If not correct ,could you pls show me your expect correct output?

 

 

Best Regards

Lucien

Calculate sum of column for each date, table contains begin date and end date.pbix
Message 7 of 7
1,627 Views
0
schwibach
Frequent Visitor

‎02-12-2022 08:24 AM

Thank you for the support and sorry for making it harder than it should be. The remote workstation with Power BI installed has no browser access and getting the data out is not as straightforward as I'd like it to be. I hope this makes it easier.

 

idAnzahlbeginnendeschuljahrDatum
8a8eb808-7cc9f46c-017c-caffa3f2-002e1113. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7cc9f46c-017c-caffa5d6-0101213. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501613. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7cc9f46c-017c-caffa4ec-0090913. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691013. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7cc9f46c-017c-caffa615-012d513. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691313. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7da27678-017d-a38ed930-0fd2513. Sep 2117. Sep 212021/2213. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501113. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501513. Sep 2124. Sep 212021/2213. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fa5-177a414. Sep 2115. Okt 212021/2214. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fc5-1781314. Sep 2115. Okt 212021/2214. Sep 21
8a8eb808-7d07f3b1-017d-09ce0e8c-170a214. Sep 2115. Okt 212021/2214. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501613. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501113. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501513. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7da27678-017d-a38ed930-0fd2513. Sep 2117. Sep 212021/2214. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691313. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691013. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7cc9f46c-017c-caffa5d6-0101213. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7cc9f46c-017c-caffa4ec-0090913. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7cc9f46c-017c-caffa3f2-002e1113. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7cc9f46c-017c-caffa615-012d513. Sep 2124. Sep 212021/2214. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501513. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501113. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501613. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7da27678-017d-a38ed930-0fd2513. Sep 2117. Sep 212021/2215. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691313. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691013. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7cc9f46c-017c-caffa5d6-0101213. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7cc9f46c-017c-caffa4ec-0090913. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7cc9f46c-017c-caffa3f2-002e1113. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7cc9f46c-017c-caffa615-012d513. Sep 2124. Sep 212021/2215. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fa5-177a414. Sep 2115. Okt 212021/2215. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fc5-1781314. Sep 2115. Okt 212021/2215. Sep 21
8a8eb808-7d07f3b1-017d-09ce0e8c-170a214. Sep 2115. Okt 212021/2215. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501513. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501613. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7da27678-017d-a38ed930-0fd2513. Sep 2117. Sep 212021/2216. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691313. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691013. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7cc9f46c-017c-caffa5d6-0101213. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501113. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7cc9f46c-017c-caffa4ec-0090913. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7cc9f46c-017c-caffa3f2-002e1113. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7cc9f46c-017c-caffa615-012d513. Sep 2124. Sep 212021/2216. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fa5-177a414. Sep 2115. Okt 212021/2216. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fc5-1781314. Sep 2115. Okt 212021/2216. Sep 21
8a8eb808-7d07f3b1-017d-09ce0e8c-170a214. Sep 2115. Okt 212021/2216. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501613. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501113. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501513. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7da27678-017d-a38ed930-0fd2513. Sep 2117. Sep 212021/2217. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691313. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691013. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7cc9f46c-017c-caffa5d6-0101213. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7cc9f46c-017c-caffa4ec-0090913. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7cc9f46c-017c-caffa3f2-002e1113. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7cc9f46c-017c-caffa615-012d513. Sep 2124. Sep 212021/2217. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fa5-177a414. Sep 2115. Okt 212021/2217. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fc5-1781314. Sep 2115. Okt 212021/2217. Sep 21
8a8eb808-7d07f3b1-017d-09ce0e8c-170a214. Sep 2115. Okt 212021/2217. Sep 21
8a8eb808-7cc9f46c-017c-caffa4ec-0090913. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501613. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691313. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691013. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7cc9f46c-017c-caffa5d6-0101213. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501513. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7cc9f46c-017c-caffa3f2-002e1113. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7cc9f46c-017c-caffa615-012d513. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501113. Sep 2124. Sep 212021/2218. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fa5-177a414. Sep 2115. Okt 212021/2218. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fc5-1781314. Sep 2115. Okt 212021/2218. Sep 21
8a8eb808-7d07f3b1-017d-09ce0e8c-170a214. Sep 2115. Okt 212021/2218. Sep 21
8a8eb808-7cc9f46c-017c-caffa615-012d513. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501113. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501513. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7da27678-017d-a38ed874-0f501613. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691313. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7da27678-017d-a38ed894-0f691013. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7cc9f46c-017c-caffa5d6-0101213. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7cc9f46c-017c-caffa4ec-0090913. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7cc9f46c-017c-caffa3f2-002e1113. Sep 2124. Sep 212021/2219. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fa5-177a414. Sep 2115. Okt 212021/2219. Sep 21
8a8eb808-7d07f3b1-017d-09ce0fc5-1781314. Sep 2115. Okt 212021/2219. Sep 21
8a8eb808-7d07f3b1-017d-09ce0e8c-170a214. Sep 2115. Okt 212021/2219. Sep 21
8a8eb808-7cc9f46c-017c-caffa605-0119313. Sep 2124. Sep 212021/2220. Sep 21
Message 6 of 7
1,634 Views
0
schwibach
Frequent Visitor

‎02-12-2022 04:25 AM

I got it:

anzahl = sumx(filter(dev_Blockklassen,dev_Blockklassen[Datum]='Blockschüler'[Datum]),dev_Blockklassen[Anzahl])

 

This one gives me the column... It's weird however that the relationship between the tables did not help me... Is there a more elegant way to do this?

Message 4 of 7
1,687 Views
0
Jos_Woolley
Solution Sage
Solution Sage
In response to schwibach

‎02-12-2022 04:48 AM

Highly unusual that you're using your date table to pull in values from another table. A date table should remain a date table.

Also, it sounds as if a measure might be more useful to you than a calculated column.

Unfortunately, a picture is not of much use in this case (copiable table data would be), so it's difficult to help you.

Regards

Message 5 of 7
1,684 Views
0
schwibach
Frequent Visitor

‎02-12-2022 04:21 AM

I managed to create the dates in new column with the list.dates function.

https://docs.microsoft.com/en-us/powerquery-m/list-dates

 

 

 

I managed to create this dashboard, showing me the maximum value and slicing the data as I wanted.

schwibach_0-1644668182880.png

 

 

However, I don't manage to create a sumif column in a table with all of the dates.

I have created a new table with all the different dates from the original table and I defined the 1:n relationship between the new table and the original one.

What I'd need is a sumif function that creates the sum of the column 'anzahl' for all the rows in the original table where 'datum' matches the column 'datum' in the current row of the new table. 

 

sum(dev_Blockklassen[anzahl}) gives me a much too high value that's the same for each row.

Message 3 of 7
1,689 Views
0
Jos_Woolley
Solution Sage
Solution Sage

‎02-12-2022 01:26 AM

Hi,

A small dataset with expected results would be useful.

Regards

Message 2 of 7
1,706 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All