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.

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors