Blank Grand Total when blanking future months

[Jæver]

Hi, 
So i've got this visual where the current month (now September) is showing the MTD sales. When the month is over, the GS II MTD LY column should show the full month of sales for LY. 
If the month hasn't occured yet, it should show a blank, which it does here - the problem arises, because the grand total is blank as well. I would like the grand total to show the sum of the GS II MTD LY column, to see how we are doing this year compared to FYTD last year.

This is the DAX for my GS II MTD LY measure:

GS II MTD LY =

VAR MTD_SALES =

    CALCULATE(

        [Gross Sales II BC],

        SAMEPERIODLASTYEAR(

            DATESBETWEEN(

                'Calendar'[Date],

                STARTOFMONTH('Calendar'[Date]),

                TODAY()-1

            )

        )

    )

VAR CurrentMonth = MONTH(TODAY())

VAR DataMonth = MONTH(MAX('Calendar'[Date]))

VAR CurrentYear = YEAR(TODAY())

VAR DataYear = YEAR(MAX('Calendar'[Date]))

RETURN

IF(

    DataYear > CurrentYear || (DataYear = CurrentYear && DataMonth > CurrentMonth),

    BLANK(),

    IF(CurrentMonth = DataMonth, MTD_SALES, [GSII LY])

)

[Jæver]

Hi, 

Thanks for the input. It does this to my visual, which isn't what i'm looking for, unfortunately. 

[mark_endicott]

@Jæver - You are missing a condition that accounts for the total. Try the DAX below, you just need to replace the value for your MONTH YEAR column inside HASONEVALUE

GS II MTD LY =

VAR MTD_SALES =
    CALCULATE (
        [Gross Sales II BC],
        SAMEPERIODLASTYEAR (
            DATESBETWEEN (
                'Calendar'[Date],
                STARTOFMONTH ( 'Calendar'[Date] ),
                TODAY () - 1
            )
        )
    )
VAR CurrentMonth =
    MONTH ( TODAY () )
VAR DataMonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR CurrentYear =
    YEAR ( TODAY () )
VAR DataYear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Calendar'[MONTH-YEAR] ),
            IF (
                DataYear > CurrentYear
                    || ( DataYear = CurrentYear
                    && DataMonth > CurrentMonth ),
                MTD_SALES,
                BLANK ()
            ),
        [GSII LY]
    )

If this works, please accept as the solution for others with the same challenge - it helps with visibility.

[Jæver]

Hi, 

Thanks for the input. It does this to my visual, which isn't what i'm looking for, unfortunately. 

[mark_endicott]

@Jæver - Sorry that's my bad, I got the IF results the wrong way around. This should work:

GS II MTD LY =

VAR MTD_SALES =
    CALCULATE (
        [Gross Sales II BC],
        SAMEPERIODLASTYEAR (
            DATESBETWEEN (
                'Calendar'[Date],
                STARTOFMONTH ( 'Calendar'[Date] ),
                TODAY () - 1
            )
        )
    )
VAR CurrentMonth =
    MONTH ( TODAY () )
VAR DataMonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR CurrentYear =
    YEAR ( TODAY () )
VAR DataYear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Calendar'[MONTH-YEAR] ),
            IF (
                DataYear > CurrentYear
                    || ( DataYear = CurrentYear
                    && DataMonth > CurrentMonth ),
                BLANK (),
                MTD_SALES
            ),
        [GSII LY]
    )

[Jæver]

Thanks for the reply! This gives me the full LY numbers, which is close to what i'm aiming towards. As you can see, the FYTD (MTD LY) totals the same as the entire LY. The MTD LY column should total around 10 mio. LESS than the full last year (11.8 - 0.8). Is there any way to work around that?

[mark_endicott · Accepted solution]

@Jæver - It occured to me what you are trying to acheive this morning, so I have updated my earlier solution below. 

This one should minus the current Month to date ONLY off of the LY total. 

VAR MTD_SALES =
    CALCULATE (
        [Gross Sales II BC],
        SAMEPERIODLASTYEAR (
            DATESBETWEEN (
                'Calendar'[Date],
                STARTOFMONTH ( 'Calendar'[Date] ),
                TODAY () - 1
            )
        )
    )
VAR CurrentMonth =
    MONTH ( TODAY () )
VAR DataMonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR CurrentYear =
    YEAR ( TODAY () )
VAR DataYear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
VAR MTD_SALES_NOW =
    CALCULATE (
        MTD_SALES,
        'Calendar'[Month_Number] = CurrentMonth
            && 'Calendar'[Year_Number] = CurrentYear
    )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Calendar'[MONTH-YEAR] ),
            IF (
                DataYear > CurrentYear
                    || ( DataYear = CurrentYear
                    && DataMonth > CurrentMonth ),
                BLANK (),
                MTD_SALES
            ),
        [GSII LY] - MTD_SALES_NOW
    )

 If this works for you, please consider marking it as the solution. 

[mark_endicott]

@Jæver - Sorry it's no longer clear what result you require, are you trying to subtract the current MTD LY from the total of that Month LY? 

If yes, you require this:  

GS II MTD LY =

VAR MTD_SALES =
    CALCULATE (
        [Gross Sales II BC],
        SAMEPERIODLASTYEAR (
            DATESBETWEEN (
                'Calendar'[Date],
                STARTOFMONTH ( 'Calendar'[Date] ),
                TODAY () - 1
            )
        )
    )
VAR CurrentMonth =
    MONTH ( TODAY () )
VAR DataMonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR CurrentYear =
    YEAR ( TODAY () )
VAR DataYear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Calendar'[MONTH-YEAR] ),
            IF (
                DataYear > CurrentYear
                    || ( DataYear = CurrentYear
                    && DataMonth > CurrentMonth ),
                BLANK (),
                [GSII LY] - MTD_SALES
            ),
        [GSII LY]
    )