8 Replies Latest reply on Apr 5, 2018 1:55 PM by Deepak Rai

# Calculate Measure on Date Difference

Hi,

I have a data set where I need to calculate the net amount (difference between the "Original amount" of accounts opened - accounts closed) per each branch, I have "date opened" and "date closed" to indicate when the account got opened or closed .

I have a bar graph with all the sum of opened account amounts for last 2 quarters per branch

I have a bar graph with all the sum of Closed account amounts for last 2 quarters per branch

I want to create a bar graph which shows the difference between the above two graphs (Net Income = Amounts Opened - Amounts Closed)

• ###### 1. Re: Calculate Measure on Date Difference

If I am getting it right, you have already answered your question Create a calculated field for the difference -> Amounts Opened - Amounts Closed (Sum[Amounts Opened]- Sum[Amounts Closed] )and  create a bar out of that.

Hope this helps.

• ###### 2. Re: Calculate Measure on Date Difference

Hi Soham,

There are two parts and your are addressing the last part, but I am having trouble in the first part of it, Let me explain the same.

In my data I just have current amount . Amount opened depends on the "opening date" field and Amount closed depends on "closing date" field. so I have to create 2 calculated fields

Part - 1

1. Amounts gained = sum(current amount) when opening date = 2 quarter

2.  Amounts Lost= sum(current amount) when Closing date = 2 quarter

Part - 2

and then apply your formula of Difference -> Amounts gained - Amounts Lost

• ###### 3. Re: Calculate Measure on Date Difference

Hi Hrishikesh,

Could you attach a workbook with sample data?

Thanks,

Shiva.

• ###### 4. Re: Calculate Measure on Date Difference

Hi Shiva,

Thanks

• ###### 5. Re: Calculate Measure on Date Difference

Thanks Hrishikesh, while I work on this I'd like to tag Deepak Rai for his take on this.

• ###### 6. Re: Calculate Measure on Date Difference

Hi Hrishi,

Here it is. I used LODs and You Need to use a Range of dates and need not to put any date on filter. Your Last 2 Quarters are between 1st oct and 31st March.

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread.

1 of 1 people found this helpful
• ###### 7. Re: Calculate Measure on Date Difference

Thanks Deepak,

This is what I was looking for, Is there a way to make the date selection dynamic ?, instead of the static dates mentioned in LOD.

Thanks

• ###### 8. Re: Calculate Measure on Date Difference

The Easiest way is Pivot your data. I think you are On SQL Server and You can Pivot these 2 Filelds of Date open and Date Closed. Make a single data  and which can be used to Control the dates with a relative filter. Then calculate separate values of gained, lost and difference. That is one approach

Other approach is via parameter, it all depends how you structure your data as you need difference for same period.

Can you take a sec to Close Thread by Marking my Reply CORRECT, The CORRECT button in under my Reply in main Thread, Not in email

Thanks

Deepak

1 of 1 people found this helpful