showing value of last year

[Johan]

hi,

I want to show the value of last year, of the selected month.

So if I select month 1703 (march 2017), it should also show the valule of last year: 1603 (march 2016)

Thanks,

Johan

[vanessafvg]

@Johan

py value = calculate(sum(value), SAMEPERIODLASTYEAR(date[date]))

[Johan]

Thanks.

I forgot to mention that it's not based on a date table, but a financial periods table. Thus

..

1610

1611

1612

1613 (closing period)

1701

1701

etc.

so when 1703 is selected, 1603 is the comparing period.

[Eric_Zhang]

---

@Johan wrote:

Thanks.

I forgot to mention that it's not based on a date table, but a financial periods table. Thus

..

1610

1611

1612

1613 (closing period)

1701

1701

etc.

so when 1703 is selected, 1603 is the comparing period.

---

@Johan

You need a calendar table as

dimdate = 
ADDCOLUMNS (
    CALENDAR ( "2016-01-01", "2017-12-01" ),
    "YearMonth", CONCATENATE (
        RIGHT ( YEAR ( [Date] ), 2 ),
        LEFT ( CONCATENATE ( "0", MONTH ( MONTH ( [Date] ) ) ), 2 )
    )
)

Then link the calendar table to your table. After that @vanessafvg's SAMEPERIODLASTYEAR measure could work.

By the way, what is 1613? There's no such a 13 month.

[Johan · Accepted solution]

Thanks for your input.

1613 is the closing period of the accounting system. It's like between december and january. It's quite common in finance to have this period. Also periods can be 4 week periods,  than you would have 13 regular periods and 1 closing. So a regular calendar month won't do.

In the period table I have now created to columns, for year (16) and period (1,2,3, etc.), In this way I can create the formula to show year-1 and period = current period.

[vanessafvg]

@Johan  would it not make sense to marry your period data with your date table otherwise its gonna be a slog to use date type functions