Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
py value = calculate(sum(value), SAMEPERIODLASTYEAR(date[date]))
Proud to be a Super User!
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 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.
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.
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.
@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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |