Missing subtotal - displaying as blank

[velvetine_123]

Hi,

I have created last 12 months visual by duplicating the calendar table. Currently i have a measure with the following DAX

TravelSpend12M =

var _max = max('Calendar'[Date])

var _12mth = EOMONTH(_max, -12)

var results =

    IF(HASONEVALUE('Calendar (2)'[Date]) &&

        MAX('Calendar (2)'[Date]) <= _max &&

        MIN('Calendar (2)'[Date]) > _12mth ,

    CALCULATE(

        SUM('Travell'[Monthly Travel Spend]),

        FILTER(ALL('Calendar'[Date], 'Calendar'[Year], 'Calendar'[Quarter & Year]),

        'Calendar'[Date] = VALUES('Calendar (2)'[Date]) )

    ),

    BLANK()

    )

RETURN

results

With this DAX, i have created a matrix table displaying the monthly travel spend and date as columns. The issue is when i add another data at rows such as category, they are not displaying the group subtotal. I believe it might be due to the missing connection between the second calendar table and fact table (Travel). How can i amend the DAX to allow category to be calculated as total as well.

 
Sample table as below : 

Date is from Calendar (2), no connection to any other tables.

Travel Category Detail is from fact table (Travel)

Appreciate your help in this. Thanks !

[Jihwan_Kim]

Hi,

Please try something like below whether it suits your requirement.

TravelSpend12M =
VAR _max =
    MAX ( 'Calendar'[Date] )
VAR _12mth =
    EOMONTH ( _max, -12 )
VAR results =
    IF (
        MAX ( 'Calendar (2)'[Date] ) <= _max
            && MIN ( 'Calendar (2)'[Date] ) > _12mth,
        CALCULATE (
            SUM ( 'Travell'[Monthly Travel Spend] ),
            FILTER (
                ALL ( 'Calendar'[Date], 'Calendar'[Year], 'Calendar'[Quarter & Year] ),
                'Calendar'[Date] = VALUES ( 'Calendar (2)'[Date] )
            )
        ),
        BLANK ()
    )
RETURN
    results

[velvetine_123]

Hi, thanks for replying. Unfortunately it doesn't solve the problem im still not getting any column subtotal as it remains blank

[Anonymous]

Hi @velvetine_123 ,

Can you please provide example data for the table? For a matrix does not reproduce the effect of your DAX code and implementation.

Best Regards

Yilong Zhou

[velvetine_123]

they are honestly a very simple table. It looks like this :

Date	Category	Travel Spend
31/7/2024	Airfare	$100
30/6/2024	Non-Travel	$50
31/5/2024	Hotel	$100
30/4/2024	Airfare	$300

The data model is connecting this Date to Calendar [Date]. The Calendar (2) table is an independent table with no connection.