Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CaptainCrewe
Frequent Visitor

Semi-Additive measure problem

Hello

 

I've been trying without success to create a measure that returns an average balance amount for each day, including weekends and holidays. Balances are only available for each business day, with IDs for Date (an integer version of YYYYMMDD) and two other dimensions (Shareholder and Shareclass). Here's a sample from the Fact_Shareholders table showing 3 missing dates at the end of the year (01 JAN was on a Monday):

 

 SemiAdditive1.png

 

The end goal is to produce an average over various periods (MTD, Last Month, Last December). This average needs to carry forward the amount from the last business day through the weekend (and any holiday), assuming those days are within the same month. So I am aiming to sum the daily amounts within a month, as extended through non-working days, and then divide by the true number of days in a given month.

 

These averages will most likely be grouped up by Shareholder for all Shareclasses (a shareholder is equivalent to an account holder; a shareclass is equivalent to an asset type, like a stock or bond).

 

I've found several articles dealing with semi-additive measures, but I can't get them to work. Several attempts are using LASTNONBLANK, like:

 

SumDaily3 = CALCULATE(SUM(Fact_Shareholders[Amount_USD]), LASTNONBLANK(Fact_Shareholders[DateID], CALCULATE(COUNT(Fact_Shareholders[Amount_USD]))))

 

 

Others take a FILTER approach:

 

SumDaily5 = CALCULATE(SUM(Fact_Shareholders[Amount_USD]), FILTER(ALL(Dates[DateID]), Dates[DateID] <= MAX(Dates[DateID])))

 

 

All are consistent in not showing any data for the weekend and holiday dates:

 

SemiAdditive2.png

 

The closest I've managed to get is with:

 

SumDaily2 = CALCULATE(SUM(Fact_Shareholders[Amount_USD]), FILTER(ALL(Dates), Dates[FullDate] >= DATE(2017,12,1)), FILTER(ALL(Dates), Dates[FullDate] <= MAX(Dates[FullDate])))

 

 

But that's using a hard-coded Date in the first filter. I tried using a FirstOfMonth measure (STARTOFMONTH(Dates[FullDate])), which shows the correct date but not the correct aggregation (SumDaily2a). There again, even SumDaily2 isn't aggregating as I need it to. The number on 02 December 2017 (a Saturday) should be 418,290,964.94. And the calculation isn't starting afresh at the beginning of a new month.

 

SemiAdditive3.png

 

Like many DAX newbies, I've tried many things, wrapping elements in CALCULATE in case that makes a difference, generally flailing around without proper understanding. Any help towards getting a better understanding and/or the desired answer would be much appreciated.

 

In summary, the goal is to determine the average Shareholder amount both by month and also within a month (so as to show a MTD average). The approach I was trying to take was to generate balances on each day in one measure (the various SumDailys above), including weekends and holidays, and then average those in another measure. Perhaps there's a more elegant approach than can go to the average directly. As an alternative, I could try to generate extra rows during the Power Query import. But at nearly 14 million rows already in Fact_Shareholders, I was hoping to avoid the extra bulk.

 

Many thanks and regards

 

Sebastian Crewe

1 ACCEPTED SOLUTION

For those that follow, here are the elements I made to get this working:

 

The formula to derive the amounts to carry forward through weekends and holidays is shown below.  This was extended from the helpful suggestion made by v-ljerr-msft earlier in this thread.

 

Last Non-blank Amount = 
VAR currentDate =
    MAX ( Dates[FullDate] )
VAR LNBDate =
    CALCULATE (
        LASTNONBLANK ( Dates[FullDate], CALCULATE ( COUNTROWS ( Fact_Shareholders ) ) ),
        FILTER ( ALL ( Dates[FullDate] ), Dates[FullDate] <= currentDate )
    )
RETURN
    CALCULATE (
        SUM ( Fact_Shareholders[Amount_USD] ),
        FILTER (
            ALL ( Dates ),
            Dates[FullDate] = LNBDate
        )
    )

The next problem was to generate a running sum of these amounts.  Although diverted into the realms of calculated tables etc, this turned out to be a simple formula:

 

 

Cumulative Amount MTD = CALCULATE(SUMX(DATESMTD(Dates[FullDate]), [Last Non-blank Amount]))

I have a similar YTD measure, replacing DATESMTD with DATESYTD.

 

 

For the average, I need either the day number in the current month or that in the current year:

 

Number Of Days Month = CALCULATE(
					CALCULATE(COUNTROWS(VALUES(Dates[FullDate])),
						DATESBETWEEN(Dates[FullDate], STARTOFMONTH(Dates[FullDate]), LASTDATE(Dates[FullDate]))
					)
)

Replace STARTOFMONTH with STARTOF YEAR for the equivalent yearly measure.

 

 

The month-to-date average is then simply:

 

MTD Avg = DIVIDE([Cumulative Amount MTD], [Number Of Days Month])

... with an equivalent for YTD.

 

 

It's working well.  I was also asked to provide end of month averages for the previous month and the end of the previous year.  Those needed month end dates:

 

LastDayOfPreviousMonth = EOMONTH(MAX(Dates[FullDate]), -1)

... and:

 

 

LastDayOfPreviousYear = VAR CurrentMonth = MONTH(MAX(Dates[FullDate])) RETURN EOMONTH(MAX(Dates[FullDate]), -CurrentMonth)

I could then refer to these in the associated measures, eg:

 

 

One Month Prior Avg = VAR LastDateOfMonth = [LastDayOfPreviousMonth] RETURN CALCULATE([MTD Avg], Dates[FullDate] = LastDateOfMonth)

Here's hoping this will help someone with a similar need.

 

 

Sebastian Crewe

View solution in original post

4 REPLIES 4
v-ljerr-msft
Employee
Employee

Hi @CaptainCrewe,

 

Could you try the formula below to see if it works in your scenario? Smiley Happy

SumDaily =
VAR currentDate =
    MAX ( Dates[FullDate] )
VAR firstDateOfCurrentMonth =
    EDATE ( currentDate, -1 ) + 1
RETURN
    CALCULATE (
        SUM ( Fact_Shareholders[Amount_USD] ),
        FILTER (
            ALL ( Dates ),
            Dates[FullDate] >= firstDateOfCurrentMonth
                && Dates[FullDate] <= currentDate
        )
    )

 

Regards

Hello,

 

Many thanks indeed for your consideration of the problem.  I have tried the measure definition you suggested but sadly it's not working quite yet.  In the screenshot below Shareholder Amount ($) is the source data, SumDaily2 is per my original post and SumDaily6 is per your definition.

 

You'll see that the SumDaily6 figures aren't starting at the beginning of the month.  So I tried breaking down the elements of your suggestion.  The CurDate measure is defined as MAX(Dates[FullDate]) and FirstDateCurMonth is EDATE([CurDate], -1) + 1.  In other words, these measures are explicit definitions of the variables in your definition.

 

I'm not sure if these explicit measures would have the same values as when they are declared as variables, ie per your measure definition.  That variation amongst returned values is what makes DAX so confusing.

 

Since FirstDateCurMonth isn't returning the actual first date of the current month, I tried using my FirstOfMonth measure, defined as STARTOFMONTH(Dates[FullDate]).  But that didn't help.  The results are on the RHS in SumDaily7.

 

Meanwhile, I'm wondering if the approach is the correct one.  Even when SumDaily2 and SumDaily6 (which follow the same pattern as your suggestion) return numbers for the weekends and holidays , the cumulative sum is not being used on the next day for which there is a source value.  The SumDaily2 figure on 04 DEC is just the sum of the actual values on 01 DEC and 04 DEC, not a running total.

 

Do you think this should be do-able in DAX or should I be looking to fill in the missing fact rows using SQL or the Query Editor?

 

Thanks so much

 

SemiAdditive4.png

I've pressed on and found a formula that seems to work for my Last Non-Blank Amount.

 

Last Non-blank Amount = 
VAR currentDate =
    MAX ( Dates[FullDate] )
VAR LNBDate =
    CALCULATE (
        LASTNONBLANK ( Dates[FullDate], CALCULATE ( COUNTROWS ( Fact_Shareholders ) ) ),
        FILTER ( ALL ( Dates[FullDate] ), Dates[FullDate] <= currentDate )
    )
RETURN
    CALCULATE (
        SUM ( Fact_Shareholders[Amount_USD] ),
        FILTER (
            ALL ( Dates ),
            Dates[FullDate] = LNBDate
        )
    )

This shows the value I want for each date, per the following:

 

 

SemiAdditive5.png

 

This seemed promising, and I thought I'd derive a table expression using this so as to calculate the desired average on any given date (Sum of LNB Amount MTD / Number of Days).  I experimented in DAX Studio with the following:

 

EVALUATE
CALCULATETABLE(
	ADDCOLUMNS(
		SUMMARIZE(
			FILTER(Fact_Shareholders, Fact_Shareholders[ShareholderID] = 3558),
			Dates[FullDate],
			Fact_Shareholders[ShareholderID]
		),
		"LNBAmount", [Last Non-blank Amount]
	),
	ALL(Dates[FullDate])
)

(That ShareholderID value corresponds to the filter I'm using in Power BI Desktop for the screenshots)

 

 

But no; the dates for which there is no underlying data in the fact table are missing again:

 

SemiAdditive6.png

 

Grrr, bother and pish.

 

Can anyone confirm that this is a sensible route to be pursuing?  Why do all dates show in Power BI Desktop but not via DAX Studio?  I'm sure I'm making some elementary errors here and would be grateful to all who can point them out.

 

Thanks and regards

 

Sebastian

For those that follow, here are the elements I made to get this working:

 

The formula to derive the amounts to carry forward through weekends and holidays is shown below.  This was extended from the helpful suggestion made by v-ljerr-msft earlier in this thread.

 

Last Non-blank Amount = 
VAR currentDate =
    MAX ( Dates[FullDate] )
VAR LNBDate =
    CALCULATE (
        LASTNONBLANK ( Dates[FullDate], CALCULATE ( COUNTROWS ( Fact_Shareholders ) ) ),
        FILTER ( ALL ( Dates[FullDate] ), Dates[FullDate] <= currentDate )
    )
RETURN
    CALCULATE (
        SUM ( Fact_Shareholders[Amount_USD] ),
        FILTER (
            ALL ( Dates ),
            Dates[FullDate] = LNBDate
        )
    )

The next problem was to generate a running sum of these amounts.  Although diverted into the realms of calculated tables etc, this turned out to be a simple formula:

 

 

Cumulative Amount MTD = CALCULATE(SUMX(DATESMTD(Dates[FullDate]), [Last Non-blank Amount]))

I have a similar YTD measure, replacing DATESMTD with DATESYTD.

 

 

For the average, I need either the day number in the current month or that in the current year:

 

Number Of Days Month = CALCULATE(
					CALCULATE(COUNTROWS(VALUES(Dates[FullDate])),
						DATESBETWEEN(Dates[FullDate], STARTOFMONTH(Dates[FullDate]), LASTDATE(Dates[FullDate]))
					)
)

Replace STARTOFMONTH with STARTOF YEAR for the equivalent yearly measure.

 

 

The month-to-date average is then simply:

 

MTD Avg = DIVIDE([Cumulative Amount MTD], [Number Of Days Month])

... with an equivalent for YTD.

 

 

It's working well.  I was also asked to provide end of month averages for the previous month and the end of the previous year.  Those needed month end dates:

 

LastDayOfPreviousMonth = EOMONTH(MAX(Dates[FullDate]), -1)

... and:

 

 

LastDayOfPreviousYear = VAR CurrentMonth = MONTH(MAX(Dates[FullDate])) RETURN EOMONTH(MAX(Dates[FullDate]), -CurrentMonth)

I could then refer to these in the associated measures, eg:

 

 

One Month Prior Avg = VAR LastDateOfMonth = [LastDayOfPreviousMonth] RETURN CALCULATE([MTD Avg], Dates[FullDate] = LastDateOfMonth)

Here's hoping this will help someone with a similar need.

 

 

Sebastian Crewe

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.