cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lancea
Helper I
Helper I

Report for displaying Actual and Estimated data

I am really having trouble generating a report that was simple to create in Excel. I have the following tables:

 

Date Table:

DateID

Month

Year

Date(CalculatedColumn)

1

11

2020

2020-11-1

2

12

2020

2020-12-1

3

1

2021

2021-1-1

4

2

2021

2021-2-1

5

3

2021

2021-3-1

6

4

2021

2021-4-1

7

5

2021

2021-5-1

8

6

2021

2021-6-1

9

7

2021

2021-7-1

10

8

2021

2021-8-1

11

9

2021

2021-9-1

12

10

2021

2021-10-1

13

11

2021

2021-11-1

14

12

2021

2021-12-1

 

Savings Table:

SavingsID

DateID

MonthlySavings

1

1

100

2

2

100

3

2

50

4

3

100

5

3

100

6

4

50

7

4

50

8

5

75

9

6

75

 

I want to come up with the following report, which is YTD Report and Forecast for 2021 (I created this in Excel):

lancea_0-1623639015809.png

Please help, as I am new to Power BI and having trouble generating the report above. I can also upload my Excel file if you need.

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @lancea 

Please check the below picture and the sample pbix file's link down below.

 

Picture1.png

 

Monthly Saving Total =
VAR currentyear =
MAX ( Dates[Year] )
VAR newtable =
FILTER (
SUMMARIZE (
ALL ( Dates ),
Dates[Year],
Dates[Month & Year],
"@monthlysavings", SUM ( Savings[MonthlySavings] )
),
Dates[Year] = currentyear
)
RETURN
IF (
ISFILTERED ( Dates ),
IF (
ISBLANK ( SUM ( Savings[MonthlySavings] ) ),
AVERAGEX ( newtable, [@monthlysavings] ),
SUM ( Savings[MonthlySavings] )
)
)
 
 
YTD Savings =
VAR currentyear =
MAX ( Dates[Year] )
VAR currentdate =
MAX ( Dates[Date] )
RETURN
IF (
ISFILTERED ( Dates ),
SUMX (
FILTER (
ALL ( Dates ),
Dates[Year] = currentyear
&& Dates[Date] <= currentdate
),
[Monthly Saving Total]
)
)
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
Jihwan_Kim
Community Champion
Community Champion

Hi, @lancea 

Please check the below picture and the sample pbix file's link down below.

 

Picture1.png

 

Monthly Saving Total =
VAR currentyear =
MAX ( Dates[Year] )
VAR newtable =
FILTER (
SUMMARIZE (
ALL ( Dates ),
Dates[Year],
Dates[Month & Year],
"@monthlysavings", SUM ( Savings[MonthlySavings] )
),
Dates[Year] = currentyear
)
RETURN
IF (
ISFILTERED ( Dates ),
IF (
ISBLANK ( SUM ( Savings[MonthlySavings] ) ),
AVERAGEX ( newtable, [@monthlysavings] ),
SUM ( Savings[MonthlySavings] )
)
)
 
 
YTD Savings =
VAR currentyear =
MAX ( Dates[Year] )
VAR currentdate =
MAX ( Dates[Date] )
RETURN
IF (
ISFILTERED ( Dates ),
SUMX (
FILTER (
ALL ( Dates ),
Dates[Year] = currentyear
&& Dates[Date] <= currentdate
),
[Monthly Saving Total]
)
)
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Thank you so much! It works!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors