topic Re: How to create a new table according the dates in DAX Commands and Tips

How to create a new table according the dates

Aidarbek — Thu, 28 Nov 2024 13:33:06 GMT

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:

SummaryTable =

VAR Seldate=SELECTEDVALUE('Sheet1 (2)'[Current 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)

)

Also tried without VAR and insert as "<selectedvalue", but also failed.

Thank you!

Re: How to create a new table according the dates

bhanu_gautam — Thu, 28 Nov 2024 13:37:35 GMT

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 table

SummaryTable =
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.

Re: How to create a new table according the dates

Aidarbek — Thu, 28 Nov 2024 13:50:19 GMT

I can't understand why, but all the amount accounted in one group:

Re: How to create a new table according the dates

FreemanZ — Thu, 28 Nov 2024 14:14:09 GMT

hi Aidarbek ,

this wont work, as calculated tables is not supposed to react on visual changes, like slicer selection.

Re: How to create a new table according the dates

Aidarbek — Thu, 28 Nov 2024 14:17:25 GMT

ok, thank you for the information!

Will try to find other way!

Re: How to create a new table according the dates

FreemanZ — Thu, 28 Nov 2024 14:25:34 GMT

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