cancel
Showing results for
Did you mean:
Helper I

## Calculate and Sum help for the prior year as a number

I have 2 tables, a data set and a date table. The dataset has records for each day, the date table has a row for each day, another column for the year, and another column for the specific way the org breaks up the year into periods. Sample below.

In my visual i use the year and period from the date table and the amount from the data table.

I want a measure that gets the sum of the amount in the same period but for the prior year. I thought

CALCULCATE(SUM('Data'[Amount]),'Date'[year]-1) but that didnt work. I dont think my understanding of this is sound.

The measure for date 2021-02-02 would read 1000 as this was the amount for period 18 in 2020 (2020-1).

Data table (as an example)

``````+------------+------+--------+
|    date    | type | amount |
+------------+------+--------+
| 2020-01-12 | a    |   1500 |
| 2020-07-04 | b    |   1000 |
| 2021-01-15 | a    |   6000 |
| 2021-02-02 | b    |   1020 |
+------------+------+--------+``````

Date table (the dates are continious however just as an example)

``````+------------+------+--------+
|    date    | year | period |
+------------+------+--------+
| 2020-01-12 | 2020 |      1 |
| 2020-07-04 | 2020 |     18 |
| 2021-01-15 | 2021 |      1 |
| 2021-02-02 | 2021 |     18 |
+------------+------+--------+``````

1 ACCEPTED SOLUTION
Community Champion

Hi, @User7664

In the sample data, the Dates-Table is not continuous, because there is a gap between 2020 Feb - 2021 Feb, and this cannot be set as a date table.

Without setting up the new Dates-Table, I tried to create a measure like the below.

The link to the pbix file is down below.

Total Amount Previous Year =
VAR currentyear =
SELECTEDVALUE ( Dates[Year] )
VAR oneyearbefore = currentyear - 1
VAR currentperiod =
SELECTEDVALUE ( Dates[Period] )
VAR result =
CALCULATE (
[Total Amount],
FILTER (
ALL ( Dates ),
Dates[Year] = oneyearbefore
&& Dates[Period] = currentperiod
)
)
RETURN
result

7 REPLIES 7
Community Champion

Hi,

I am not sure how your model is built, but please try the below.

MEASURE_NEW =

VAR

preivousyear = selectedvalue(Date'[year])-1

VAR

result = CALCULCATE( SUM('Data'[Amount]), 'Date'[year] = previousyear)

RETURN

result

if it is not working, please kindly share the pbix file, if possible.

Thank you.

Helper I

Ive attached a PBIX which sort of mirrors what i have in the network, at least the layout. The date table i have on the network is obviously continious.

Super User III

Hi,

These measure works.  In the Formatting pane, please ensure that Totals are turned off.

``Amounts = SUM(Data[Amount])``
``Measure = CALCULATE([Amounts],FILTER(ALL(Dates[Year]),Dates[Year]=values(Dates[Year])-1))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

Hi, @User7664

In the sample data, the Dates-Table is not continuous, because there is a gap between 2020 Feb - 2021 Feb, and this cannot be set as a date table.

Without setting up the new Dates-Table, I tried to create a measure like the below.

The link to the pbix file is down below.

Total Amount Previous Year =
VAR currentyear =
SELECTEDVALUE ( Dates[Year] )
VAR oneyearbefore = currentyear - 1
VAR currentperiod =
SELECTEDVALUE ( Dates[Period] )
VAR result =
CALCULATE (
[Total Amount],
FILTER (
ALL ( Dates ),
Dates[Year] = oneyearbefore
&& Dates[Period] = currentperiod
)
)
RETURN
result

Helper I

This works but its oddly more complex than i had expected. Its given me plenty of learnings tho. Thank you!

Helper I

This didn't work. The output was blank.

The data table and date table are joined on the date column.

I'd love to share my pbix but I cannot export files from my network. 😞

Community Champion

Ok, Noted.

If your network does not allow you to export any files, then perhaps you can create a sample file by yourself.

It will be much more helpful for people to look into your problems to solve if you can just create a sample file and share it.

Thank you.

Announcements