Work on Date

[Carlo1975]

Hi I have this table:

Portfolio	Amount	Date
Pippo	150	01/01/2020
pluto	200	01/02/2021
Paperino	265	01/01/2022
Topolino	5896	01/03/2021
Minnie	1478	01/03/2021
Paperone	1265	01/04/2021
Pippo	2158	01/01/2022
pluto	265	01/08/2022
Paperino	5896	01/06/2020
Topolino	1478	01/10/2021
Minnie	150	01/08/2021
Paperone	200	01/09/2021

From this table I have to do a MAtrix Table:

1) Only Portfolios and amounts about Current Year (in this case 2021, but next year automatically 2022)

2) I have to show 3 columns about amount: The first with the total about current Year. The second one with the total about the past months (in this case only january) and finally a column with total about next months (from february to december).

The result will be:

	Total	Actual	Project
pluto	465	200	265
Topolino	7374	0	7374
Minnie	1628	0	1628
Paperone	1465	0	1465

Is it possible?

[amitchandak]

@Carlo1975 , Try with help from time intelligence. Choose Current month in slcier or end you calendar there.

Actual  = CALCULATE(SUM(Table[Amount]),DATESMTD('Date'[Date]))

Total  = CALCULATE(SUM(Table[Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

or

Total  = CALCULATE(SUM(Table[Amount]),previousyear('Date'[Date]))

Project = [Total] -[Actual]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Carlo1975]

Is it possible without using slicer?

insert instead DATESMTD something like month(today())....