Forum Discussion

CaptainCrewe's avatar
CaptainCrewe
Frequent Visitor
8 years ago
Solved

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,...
Need Help
  • CaptainCrewe's avatar
    CaptainCrewe
    8 years ago

    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

CaptainCrewe's avatar
CaptainCrewe
Frequent Visitor
8 years ago

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:

 

 

 

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:

 

 

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

CaptainCrewe's avatar
CaptainCrewe
Frequent Visitor
8 years ago

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