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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi CaptainCrewe,

 

Could you try the formula below to see if it works in your scenario? :smileyhappy:

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

  • CaptainCrewe's avatar
    CaptainCrewe
    Frequent Visitor
    8 years ago

    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

     

    • 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