How to create a new table according the dates

[Aidarbek]

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!

[FreemanZ · Accepted solution]

hi @Aidarbek ,

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

[Aidarbek]

ok, thank you for the information!

Will try to find other way!

[FreemanZ]

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

[Aidarbek]

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