DAX Closing and Opening Balances in Total

[Anonymous]

Hi,

We currently have a requirement to create a table which shows the opening and closing balances of revenue generating employees (FTE) at various time periods (Year, Qtr, Month & Week).  I've managed to get this working at each level but the issue I'm having is at the subtotal of the table. 

For example, if there are four fiscal quarters in the selected year and Q1 opening balance is 100 and Q4 closing balance is 120, then I I need to show Opening = 100 and Closing = 120 in the subtotal bar.  I cannot get this to work.

The logic I am using for the measure is:

Previous FTE:=

VAR currentFYYear = IF(VALUE(MIN('Date'[FiscalQuarterNumber])) = 4, VALUE(MIN('Date'[FYearNumber])) -1, VALUE(MIN('Date'[FYearNumber])))

VAR currentFiscalQuarter = IF(VALUE(MIN('Date'[FiscalQuarterNumber])) = 4,1, VALUE(MIN('Date'[FiscalQuarterNumber])-1))

RETURN

    CALCULATE (

       [FTE],

        FILTER (

            ALL ( 'Date' ),

            VALUE('Date'[FiscalQuarterNumber]) = currentFiscalQuarter

                && VALUE('Date'[FYearNumber])

                = currentFYYear

        )

    )

Appreciate any help.

Thanks,

Mani

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi Mani,

Can you share a sample file, please? Please mask the sensitive parts first. The root cause could be the context. Maybe you can try the function hasonevalue-function-dax.

Best Regards,
Dale

[Anonymous]

Hi, thanks for replying.

I cant share the file as it uses a live connection to SSAS.  But a bit more detail may help:

I first create a measure for each time period: Week, Month, Qtr, Year using the logic (changing as appropriate for the time period):

FTE Open (Quarter):=
VAR currentFYYear = IF(VALUE(MIN('Date'[FiscalQuarterNumber])) = 1, VALUE(MIN('Date'[FYearNumber])) -1, VALUE(MIN('Date'[FYearNumber])))
VAR currentFiscalQuarter = IF(VALUE(MIN('Date'[FiscalQuarterNumber])) = 1,4, VALUE(MIN('Date'[FiscalQuarterNumber])-1))
RETURN
    CALCULATE (
       [FTE],
        FILTER (
            ALL ( 'Date' ),
            VALUE('Date'[FiscalQuarterNumber]) = currentFiscalQuarter
                && VALUE('Date'[FYearNumber])
                = currentFYYear
        )
    )

After this Ive created a final measure which uses a SWITCH statement that checks whether a time period is selected using the HASONEVALUE function, e.g.:

FTE (Opening):=
CALCULATE(
 SWITCH(TRUE()
  ,HASONEVALUE('Date'[Fiscal Week]),[FTE Open (Week)] 
  ,HASONEVALUE('Date'[Month Name]),[FTE Open (Month)]
  ,HASONEVALUE('Date'[Fiscal Quarter]),[FTE Open (Quarter)]
  ,HASONEVALUE('Date'[Fiscal Year]),[FTE Open (Year)]
 , BLANK())
,FILTER('Date',[Billable Amount] > 0))

The issue is that this doesn't equate to TRUE at the total level, so the value is blank.  What I need is for the value in the total bar to show the value from the first period in the selection (e.g. the value from Qtr 1) and the value from the last period in the selection (e.g. Qtr 4).  These then become the opening and closing vlaues in the total.  If I use the calculations on their own then the total shows the correct value, but I need to use a single measure and switch between the different values depending on which level of the datae hierarchy the user is at.

Hope that makes sense?

Thanks.

[v-jiascu-msft]

Hi @Anonymous,

I found a solution based on your formulas. The Switch works in order, which means it iterates the condition from the nearest to the farthest. So we can do it this way.

FTE (Opening) :=
CALCULATE (
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Date'[Fiscal Week] ), [FTE Open (Week)],
        HASONEVALUE ( 'Date'[Month Name] ), [FTE Open (Month)],
        HASONEVALUE ( 'Date'[Fiscal Quarter] ), [FTE Open (Quarter)],
        HASONEVALUE ( 'Date'[Fiscal Year] ), [FTE Open (Year)],
        NOT HASONEVALUE ( 'Date'[Fiscal Year] ), [FTE Open (Quarter)],
        BLANK ()
    ),
    FILTER ( 'Date', [Billable Amount] > 0 )
)

Please give it a try.

Best Regards,
Dale