Compare sameperiodlastyear, from date to date

[boby]

Hi all,

eg : total amount = CALCULATE(sum(TABLE1[amount]); LASTDATE(TABLE1[Date]))  = 23

Total amount sameperiodlastyear =  CALCULATE([total amount ]; SAMEPERIODLASTYEAR(TABLE1[Date]))  = 31

I'd like to compare 2015 and 2016. from date to date :

today is  23/12/2016 : I want to compare from 1/12/2016 to 23/12/2016 with 01/12/2015 to 23/12/2015.

Thanks in advance for the assistance.

Date	Amount	CumulatifAmont	Date	Amount	CumulatifAmont
01/12/2015	1	1	01/12/2016	1	1
02/12/2015	1	2	02/12/2016	1	2
03/12/2015	1	3	03/12/2016	1	3
04/12/2015	1	4	04/12/2016	1	4
05/12/2015	1	5	05/12/2016	1	5
06/12/2015	1	6	06/12/2016	1	6
07/12/2015	1	7	07/12/2016	1	7
08/12/2015	1	8	08/12/2016	1	8
09/12/2015	1	9	09/12/2016	1	9
10/12/2015	1	10	10/12/2016	1	10
11/12/2015	1	11	11/12/2016	1	11
12/12/2015	1	12	12/12/2016	1	12
13/12/2015	1	13	13/12/2016	1	13
14/12/2015	1	14	14/12/2016	1	14
15/12/2015	1	15	15/12/2016	1	15
16/12/2015	1	16	16/12/2016	1	16
17/12/2015	1	17	17/12/2016	1	17
18/12/2015	1	18	18/12/2016	1	18
19/12/2015	1	19	19/12/2016	1	19
20/12/2015	1	20	20/12/2016	1	20
21/12/2015	1	21	21/12/2016	1	21
22/12/2015	1	22	22/12/2016	1	22
23/12/2015	1	23	23/12/2016	1	23
24/12/2015	1	24	24/12/2016	1
25/12/2015	1	25	25/12/2016	1
26/12/2015	1	26	26/12/2016	1
27/12/2015	1	27	27/12/2016	1
28/12/2015	1	28	28/12/2016	1
29/12/2015	1	29	29/12/2016	1
30/12/2015	1	30	30/12/2016	1
31/12/2015	1	31	31/12/2016	1

[austinsense]

First, if you're going to do time intelligence you need a date table that you connect to your data table.

1. Contiguous dates (don't skip any days)

2. Every day once (no duplicate dates).

Second, when you write time intelligence functions, always reference the date table.

Third, Month-to-Date (MTD) is like this ... https://msdn.microsoft.com/en-us/library/ee634359.aspx

MTD Sales = CALCULATE( Table[Amount], DATESMTD(DateTable[Date]) )

Fourth, Month-to-Date Last Year (MTD LY) ... https://msdn.microsoft.com/en-us/library/ee634905.aspx

MTD Sales LY = CALCULATE( [MTD Sales], DATEADD(DateTable[Date], -1, YEAR) )

[boby]

I have a date table to connect with my data table. but i'm doing something wrong !!

M0 = CALCULATE(sum(Test[Amount]); DimDate[year] = 2015) the mesure is 31

M1 = CALCULATE(sum(Test[Amount]); DimDate[year] = 2016) the mesure is 23

MTD = CALCULATE(sum(Test[Amount]) ; DATESMTD(DimDate[Date]))  nothing why ?

M2 MTD LY = CALCULATE([MTD] ; DATEADD(DimDate[Date]; -1; YEAR)) nothing why ?

what i want is :

Result of current month : 23 (from 1/12/2016 to 23/2016

Result of last year : 23  : (from 1/12/2015 to 23/12/2015

Thank you

[Vvelarde · Accepted solution]

@boby

Maybe exist a short version but i'll go to a meeting and don't have time to optimize

MTD =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( DATESMTD ( Calendario[Date] ), Calendario[Date] <= TODAY () )
)

M2 MTD LY =
CALCULATE (
    [MTD],
    FILTER (
        DATEADD ( Calendario[Date], -1; YEAR ),
        Calendario[Date]
            <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)

[acrmorris]

@Vvelarde, there may be a shorter solution, but this worked for me first time and because of the format means I can understand what is happening in the formula. Maybe I will take a look at shortening it myself when I have a bit more experience with Power BI.

Thanks for the post

[boby]

Awesome, works perfectly.

I need to analyse this long formula after vacation.

Thank you

and Merry Christmas and a happy New year

[v-huizhn-msft]

Hi @boby,

I very gald the formula work perfectly, please mark the corresponding reply as solution, which will help more people.

Best Regards,
Angelia