Calculate and Sum help for the prior year as a number

[User7664]

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 |
+------------+------+--------+

[Jihwan_Kim]

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.

Did I answer your question? Mark my post as a solution!

[User7664]

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. 

Ive added the pbix to my google drive here

[Ashish_Mathur]

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.

[Jihwan_Kim · Accepted solution]

Hi, @User7664 

Thank you very much for your information, and it really helped me a lot to learn more about your data.

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.

Please kindly have a look, and please let me hear your feedback.

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

Did I answer your question? Mark my post as a solution!

https://www.dropbox.com/s/hc5k7dpyy4mg30c/User7664%202021%200322.pbix?dl=0 

[User7664]

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

[User7664]

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. 😞

[Jihwan_Kim]

Ok, Noted.

Did I answer your question? Mark my post as a solution!

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.