Report for displaying Actual and Estimated data

[lancea]

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):

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.

[Jihwan_Kim · Accepted solution]

Hi, @lancea 

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

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]

)

)

https://www.dropbox.com/s/u27nnf538x0nfu7/lancea.pbix?dl=0 

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

[lancea]

Thank you so much! It works!