How to calculate percentage variation considering first date on chart?

[Anonymous]

Hello, guys, 

I have a table with the balance values for different investment funds. The table contains the current balance for different months. A sample of the table is shown below:

Each fund received the first investment on different dates. As it is shown on the table "Fund 4", "Fund 5" and "Fund 6" received the first investment on August 2020, whereas "Fund 1", "Fund 2", "Fund 3" received the first investment on June 2020. 

I am trying to make a line chart that shows the variation of these funds considering the first month on the chart or the first month that has an balance value. This variation must also be dynamic considering the date filtered by the slicer. An example of the chart is shown below:

So far I was able to calculate the initial balance for each fund considering the minimum date filtered by the slicer. However I have an issue when I try to filter dates before August 2020, because funds 4, 5 and 6 received their first investment on that month. So the measure I am using is returning infinity, therefore the chart does not show the variation of those funds. 

The metrics I was able to create so far are:

Initial Balance = 

//Calculate the minimum date being filtered by the slicer
var min_date = CALCULATE(MIN('Table Investment Funds'[Date]),ALLSELECTED('Table Investment Funds'))

//Calculate the first balance for each investment fund considering the minimum date filtered by the slicer
var initial_value = SUMX(ADDCOLUMNS( SUMMARIZE('Table Investment Funds','Table Investment Funds'[Funds Name]),"Initial Balance",CALCULATE(SUM('Table Investment Funds'[Current Balance]),'Table Investment Funds'[Date]=min_date)),[Initial Balance])

return initial_value




Percentage Variation = 
SUM('Table Investment Funds'[Current Balance])/'Table Investment Funds'[Initial Balance])-1

Some results of the metrics are:

Any ideas on how to calculate properly the percentage variation considering the first date that has an value or is shown on the chart?

Thank you very much for the help!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Balance: =

VAR lastdateinmonth =

LASTNONBLANK ( 'Calendar'[Date], CALCULATE ( SUM ( Data[Balance] ) ) )

RETURN

IF (

HASONEVALUE ( 'Calendar'[Month Name] ),

CALCULATE (

SUM ( Data[Balance] ),

FILTER ( 'Calendar', 'Calendar'[Date] = lastdateinmonth )

)

)

Initial balance: =

VAR currentfundname =

MAX ( Data[Funds] )

VAR newtable =

FILTER (

ADDCOLUMNS (

SUMMARIZE (

ALLSELECTED ( Data ),

Data[Funds],

'Calendar'[Month Name],

'Calendar'[Month]

),

"@balance", [Balance:]

),

[@balance] <> 0

&& Data[Funds] = currentfundname

)

RETURN

IF ( HASONEVALUE ( 'Calendar'[Month Name] ), MINX ( newtable, [@balance] ) )

Percent variation: =

DIVIDE( [Balance:] - [Initial balance:], [Initial balance:] )

[askhanduja · Accepted solution]

Hi,

Please see the sample solution here.

You can create a data model that looks like the following:

It has the following measures:

Sum of Current Balance = 
CALCULATE(    
    SUM( factInvestmentFunds[_CurrentBalance] ),
    KEEPFILTERS( dimDates[Date] = MAX( dimDates[Date] ) )
)

------------------------------------------------------------------------------------------

Fund First Investment Date = 
VAR __SelectedDates = ALLSELECTED( dimDates[Date] )

VAR __MinSelectedDate =
MINX( __SelectedDates, dimDates[Date] )

VAR __FirstDateOfGivenFund =
CALCULATE(
    MINX(
        VALUES( factInvestmentFunds[_Date] ),
        factInvestmentFunds[_Date]
    ),
    REMOVEFILTERS( dimDates[Date] )
)

VAR __Result = MAX( __FirstDateOfGivenFund, __MinSelectedDate )

RETURN
__Result

--------------------------------------------------------------------------------------------

Fund Initial Balance = 
VAR __InitialFundInvestmentDate = [Fund First Investment Date]

VAR __Result = 
CALCULATE(
    SUM( factInvestmentFunds[_CurrentBalance] ),
    dimDates[Date] = __InitialFundInvestmentDate
)

RETURN
__Result

--------------------------------------------------------------------------------------------

Percentage Variation = 
VAR __FirstInvestmentDate = [Fund First Investment Date]

VAR __Result =
IF(
    __FirstInvestmentDate <= SELECTEDVALUE( dimDates[Date] )
    && COUNTROWS( factInvestmentFunds ) > 0,
    DIVIDE(
        [Sum of Current Balance],
        [Fund Initial Balance]
    ) - 1
)

RETURN
__Result

and leads to the following output:

Hope this helped. If it solves your issue please do mark this post as the solution so others with similar issues may find the solution and I would really appreciate a thumbs up.

[askhanduja · Accepted solution]

Hi,

Please see the sample solution here.

You can create a data model that looks like the following:

It has the following measures:

Sum of Current Balance = 
CALCULATE(    
    SUM( factInvestmentFunds[_CurrentBalance] ),
    KEEPFILTERS( dimDates[Date] = MAX( dimDates[Date] ) )
)

------------------------------------------------------------------------------------------

Fund First Investment Date = 
VAR __SelectedDates = ALLSELECTED( dimDates[Date] )

VAR __MinSelectedDate =
MINX( __SelectedDates, dimDates[Date] )

VAR __FirstDateOfGivenFund =
CALCULATE(
    MINX(
        VALUES( factInvestmentFunds[_Date] ),
        factInvestmentFunds[_Date]
    ),
    REMOVEFILTERS( dimDates[Date] )
)

VAR __Result = MAX( __FirstDateOfGivenFund, __MinSelectedDate )

RETURN
__Result

--------------------------------------------------------------------------------------------

Fund Initial Balance = 
VAR __InitialFundInvestmentDate = [Fund First Investment Date]

VAR __Result = 
CALCULATE(
    SUM( factInvestmentFunds[_CurrentBalance] ),
    dimDates[Date] = __InitialFundInvestmentDate
)

RETURN
__Result

--------------------------------------------------------------------------------------------

Percentage Variation = 
VAR __FirstInvestmentDate = [Fund First Investment Date]

VAR __Result =
IF(
    __FirstInvestmentDate <= SELECTEDVALUE( dimDates[Date] )
    && COUNTROWS( factInvestmentFunds ) > 0,
    DIVIDE(
        [Sum of Current Balance],
        [Fund Initial Balance]
    ) - 1
)

RETURN
__Result

and leads to the following output:

Hope this helped. If it solves your issue please do mark this post as the solution so others with similar issues may find the solution and I would really appreciate a thumbs up.

[Anonymous]

Hi, askhanduja, 

Thank you very much for your solution. It worked! It was very clever. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Balance: =

VAR lastdateinmonth =

LASTNONBLANK ( 'Calendar'[Date], CALCULATE ( SUM ( Data[Balance] ) ) )

RETURN

IF (

HASONEVALUE ( 'Calendar'[Month Name] ),

CALCULATE (

SUM ( Data[Balance] ),

FILTER ( 'Calendar', 'Calendar'[Date] = lastdateinmonth )

)

)

Initial balance: =

VAR currentfundname =

MAX ( Data[Funds] )

VAR newtable =

FILTER (

ADDCOLUMNS (

SUMMARIZE (

ALLSELECTED ( Data ),

Data[Funds],

'Calendar'[Month Name],

'Calendar'[Month]

),

"@balance", [Balance:]

),

[@balance] <> 0

&& Data[Funds] = currentfundname

)

RETURN

IF ( HASONEVALUE ( 'Calendar'[Month Name] ), MINX ( newtable, [@balance] ) )

Percent variation: =

DIVIDE( [Balance:] - [Initial balance:], [Initial balance:] )

[Anonymous · Accepted solution]

Hi, Jihwan_Kim, 

Thank you very much for your help. The solution you provided worked after some modifications I've made. Your measures had the function HASONEVALUE for the "month" column, however my data has repeated months, as it goes from 2019 to 2021. It was my fault!! I should have provided a complete sample data. 

However your solution was very insightful and I could make some modifications. I am providing the file with the corrections in case other people want to use it. 

Solution