Calculating a $ value for a calendar year from two date columns

[Anonymous]

Good Afternoon Forum Members,

I'm hoping that some of you are able to assist myself in what seemed like a simple task, but that is currently driving me Insane. The brief is to determine the total $ value per calendar year, when I have monthly cost value per record, with a start date in one column and the finish date in another. I'm looking to create a measure which would be used in a card visual to show $ values for 2020, 2021 and 2022 etc.

Simple Data Schema Below

Column 1 - Mthly $ Cost

Column 2 - Start date

Column 3 - Finish date

Please forgive my ignorance, I am very new to Power BI, and I expect that I'm tieing myself in knots, any help would be appreciated. I have tried to use the following, but do not understand how to parse the correct dates into the filter.

TotalCosts $ = CALCULATE(SUM('Table1'[Mthly Costs $]),FILTER(VALUES('Table1'[Start Date]), 'Table1'[Start Date] <= MAX(DateDimension[DateKey])), FILTER(VALUES('Table1'[Finish Date]), 'Table1'[Finish Date] >= MIN(DateDimension[DateKey])))

[amitchandak]

@Anonymous , try like

TotalCosts $ = CALCULATE(SUM('Table1'[Mthly Costs $]),FILTER('Table1', 'Table1'[Start Date] <= MAX(DateDimension[DateKey]) && 'Table1'[Finish Date] >= MIN(DateDimension[DateKey])))

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Maybe:

TotalCosts $ = 
  VAR __Max = MAX(DateDimension[DateKey])
  VAR __Min = MIN(DateDimension[DateKey])
  VAR __Table = 
    FILTER(
      'Table1',
      ([Start Date]>=__Min && [Finish Date]<=__Max)
    )
RETURN
  SUMX('Table1',[Mthly Costs $])

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Item	Mthly Cost $	Start Date	Finish Date
a	5000	01.01.2020	31.12.2020
b	5000	01.06.2020	30.06.2021
c	5000	01.01.2021	31.12.2021

The output would be a value per calendar year (x3 measures, one for each year being considered). Therefore the output for 2020 should as be follows based on the data range calculations:

CY20:

item a = $5000 * 12mths = $60000

item b = $5000 * 6mths of CY20 = $30000

item c = $5000 * 0mths as not in CY20 = $0

Total value expected value for CY20 = $90000

Hope this makes more sense to the community.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

First unpivot columns start date and finish date,and you will see:

Then create 3 columns as below:

year = YEAR('Test table'[Date])

Month to the end = 
 IF(DAY('Test table'[Date])=day(EOMONTH('Test table'[Date],0)),12-MONTH('Test table'[Date]),13-MONTH('Test table'[Date]))

Total = 'Test table'[Mthly Cost $]*'Test table'[Month to the end]

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[negi007]

@Anonymous I think you want to show the value for 3 sepearate years in your dashboard. You can create 3 measures for each of years as below and use them in your visual

2020_Val = CALCULATE(SUM('Line Chart%'[Value]),YEAR=2020)

2021_Val = CALCULATE(SUM('Line Chart%'[Value]),YEAR=2021)

2019_Val = CALCULATE(SUM('Line Chart%'[Value]),YEAR=2019)

let me know if it helps you