Forum Discussion
How to create a new table according the dates
Good day, everyone!
I'm new in PowerBI, could you please help me with this issue.
I have a table, like (template is made as actual table):
| Org name | Amount | Date |
| A | 1 000 | 01.01.2024 |
| b | 2 000 | 01.02.2024 |
| c | 3 000 | 01.03.2024 |
| d | 4 000 | 01.04.2024 |
| e | 5 000 | 01.05.2024 |
| f | 6 000 | 01.06.2024 |
| A | 1 000 | 01.07.2024 |
| b | 2 000 | 01.08.2024 |
| c | 3 000 | 01.09.2024 |
| d | 4 000 | 01.10.2024 |
| e | 5 000 | 01.11.2024 |
| f | 6 000 | 01.12.2024 |
This table shows the information about payments, dates and which organization should make a payment.
I have to make a slicer (dropbox) with a reporting date, and create a table with 3 column.
Org name - Payment before the reporting date - payments after the reporting date
I used to try a different ways.
I used to try to use a slicer with a column in a original table, but it didn't work.
So I created new table with a list of Current dates possible (Sheet 1 (2)).
The last one I created a list as follow:
5 Replies
ok, thank you for the information!
Will try to find other way!
FreemanZSuper User
hi Aidarbek ,
yes, instead of write calculated table, try to write plot a table visual with existing columns and measures like below:
Amount After =
VAR Seldate=SELECTEDVALUE('Sheet1 (2)'[Current date])
VAR _result=
SUMX(
FILTER(
ALLSELECTED(Sheet1),
Sheet1'[Date] > Seldate
),
Sheet1[Amount]
)
RETURN _result
Amount Before =
VAR Seldate=SELECTEDVALUE('Sheet1 (2)'[Current date])
VAR _result=
SUMX(
FILTER(
ALLSELECTED(Sheet1),
Sheet1'[Date] < Seldate
),
Sheet1[Amount]
)
RETURN _result
- bhanu_gautam
Aidarbek ,
If you don't already have a date table, you can create one using DAX:
DateTable =
ADDCOLUMNS (
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"MonthName", FORMAT ( [Date], "MMMM" ),
"YearMonth", FORMAT ( [Date], "YYYY-MM" )
)Add the Date column from the DateTable to a slicer in your report.
Create a Summary Table
Use the following DAX to create a summary tableSummaryTable =
VAR Seldate = SELECTEDVALUE('DateTable'[Date])
RETURN
SUMMARIZE(
'Sheet1',
'Sheet1'[Org name],
"Amount Before Selected Date",
CALCULATE(SUM('Sheet1'[Amount]), 'Sheet1'[Date] < Seldate),
"Amount After Selected Date",
CALCULATE(SUM('Sheet1'[Amount]), 'Sheet1'[Date] > Seldate)
)Add a table visual to your report and use the SummaryTable to display the Org name, Amount Before Selected Date, and Amount After Selected Date.
I can't understand why, but all the amount accounted in one group:
