cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
User7664
Helper I
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
Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture1.png

 

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 

View solution in original post

7 REPLIES 7
Jihwan_Kim
Community Champion
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.

 

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

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

 

User7664_0-1616355248318.png

 

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture1.png

 

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 

View solution in original post

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

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
Community Champion
Community Champion

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors