Calculating a Sum of Mrr based on the report's current time period's last date

[Anonymous]

Hi all,

I'm attempting to create a measure that calculates the Mrr of a single date based on the current context of the report. For example, if I'm looking at July, I want a sum of all the Mrr values (converted into USD) in my table that match the 31st of July in that year. If I'm looking at Q3, I want it to sum all the Mrr values in my table that match the 30th of september.

Here's the form of my data in table fAccounts:

Id, Mrr, Currency, Date(only contains dates of the final days in each months from 2014-2019)

Here's my measure so far (it works when looking at months but not when I move to quarters. In quarters it adds up the last date of all three months in the quarter):

MrrInUSD =

SUMX(FILTER('fAccounts', fAccounts[Date] = LASTDATE(dDates[Date])),

'fAccounts'[Mrr] /

LOOKUPVALUE(dExchangeRates[ExchangeRate],

dExchangeRates[Date], 'fAccounts'[Date],

dExchangeRates[Currency], 'fAccounts'[Currency]))

Thanks for the help

[Anonymous · Accepted solution]

The solution was rather simple.

The first thing I did was simplify my Mrr calculation by creating a new column (MrrUSD) with standardized Mrr values in USD (the lookup part of my original measure). Next, I used this simple measure: 

TotalMrrUSD = CALCULATE(SUM(fAccounts[MrrUSD]), FILTER(fAccounts, fAccounts[Date] = MAX(fAccounts[Date])))

Now whenever I drill down, it dynamically sums up the total mrr of the last date in a given period.

Works like a charm. Thanks for the help @v-xuding-msft and @Nathaniel_C.

[v-xuding-msft]

Hi @Anonymous ，

Can you please share a dummy file? In your formula, I saw the table of dExchangeRates and dDates.What is the form of them? If you can't post a file, please share some sample data and your expected results.

How to Get Your Question Answered Quickly

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xuding-msft, 

dExchangeRates has the form:

Date, Currency, Exchange Rate to USD (ex: 1/12/2019, AUD, 0.83)

dDates has a single column of dates (ex: 3/20/2018).

Here's some sample data and some expected results

fAccounts: Id, Mrr, Currency, Date

User 1, $30, USD, 1/31/2019

User 2, $100, USD, 1/31/2019

User 2, $100, USD, 2/28/2019

User 1, $30, USD, 3/31/2019

User 2, $100, USD, 3/31/2019

User 3, $10, USD, 3/31/2019

When my report is at the context of months, I want to see the total Mrr of Jan to be $130, Feb to be $100 and Mar to be $140.

When my report is at the context of quarters, I want to see Q1 to be $140 (adding up the last date of the quarter)

And so on...

Thank you for the help!

[v-xuding-msft]

Hi @Oliver_Freeman ,

The measure can't be filtered by date hierarchy automatically while drilling down. I created a measure to changed based on slicer.

• Create calculated columns

dDate:
last date = CALCULATE(MAX(dDates[Date]),ALLEXCEPT(dDates,dDates[Date].[Month]))

fAccounts:
Month = MONTH(fAccounts[Date])
Quarter = ROUNDUP(MONTH([Date])/3,0)
Max Month per Quarter = CALCULATE(MAX(fAccounts[Month]),FILTER(ALLEXCEPT(fAccounts,fAccounts[Quarter]),fAccounts[Quarter] <= EARLIER(fAccounts[Quarter])))

• Create a measure

Measure = 
var a = SELECTEDVALUE('Table'[Date])
var max_month = CALCULATE(MAX(fAccounts[Month]),ALL(fAccounts))
var max_quarter = CALCULATE(MAX(fAccounts[Month]),ALL(fAccounts[Quarter]))
var month = CALCULATE(SUM(fAccounts[Mrr]),FILTER(ALL(fAccounts),fAccounts[Date] = MAX(dDates[Date])))
var year = CALCULATE(SUM(fAccounts[Mrr]),FILTER(ALL(fAccounts),fAccounts[Date] = MAX(dDates[Date]) && fAccounts[Month] = max_month ))
var quarter = CALCULATE(SUM(fAccounts[Mrr]),FILTER(ALLEXCEPT(fAccounts,fAccounts[Quarter]),fAccounts[Date] = MAX(dDates[Date]) && fAccounts[Month] = MAX(fAccounts[Column]) ))
return 
SWITCH(TRUE(),a= "Month", month, a= "Year", year,a= "Quarter",quarter,month)

• Create a new table manually as slicer

Results:

I attached my sample that you can download.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft]

The measure can't be filtered by date hierarchy automatically while drilling down. I created a measure to changed based on slicer.

• Create calculated columns

dDate:
last date = CALCULATE(MAX(dDates[Date]),ALLEXCEPT(dDates,dDates[Date].[Month]))

fAccounts:
Month = MONTH(fAccounts[Date])
Quarter = ROUNDUP(MONTH([Date])/3,0)
Max Month per Quarter = CALCULATE(MAX(fAccounts[Month]),FILTER(ALLEXCEPT(fAccounts,fAccounts[Quarter]),fAccounts[Quarter] <= EARLIER(fAccounts[Quarter])))

• Create a measure

Measure = 
var a = SELECTEDVALUE('Table'[Date])
var max_month = CALCULATE(MAX(fAccounts[Month]),ALL(fAccounts))
var max_quarter = CALCULATE(MAX(fAccounts[Month]),ALL(fAccounts[Quarter]))
var month = CALCULATE(SUM(fAccounts[Mrr]),FILTER(ALL(fAccounts),fAccounts[Date] = MAX(dDates[Date])))
var year = CALCULATE(SUM(fAccounts[Mrr]),FILTER(ALL(fAccounts),fAccounts[Date] = MAX(dDates[Date]) && fAccounts[Month] = max_month ))
var quarter = CALCULATE(SUM(fAccounts[Mrr]),FILTER(ALLEXCEPT(fAccounts,fAccounts[Quarter]),fAccounts[Date] = MAX(dDates[Date]) && fAccounts[Month] = MAX(fAccounts[Column]) ))
return 
SWITCH(TRUE(),a= "Month", month, a= "Year", year,a= "Quarter",quarter,month)

• Create a new table manually as slicer

Results:

I attached my sample that you can download.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nathaniel_C]

Hi @Anonymous ,

How about create another column that holds the last date of the quarters, and change your reference to that?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel