CODEWITHSUNDEEP
reporting-servicesdaxbusiness-intelligence
Whippet
Whippet

Reputation: 1

DAX Sum All Years but Not Current Year

I have a DAX query which I'm running via SSRS to produce a yearly summarized result set, with multiple group by, filters, measures etc. but for the question I'll use a simplified version.

It looks like this:

EVALUATE 
SUMMARIZECOLUMNS(
'Years'[Year],
'Currency'[CurrencyName],
"SalesAmount", [SalesAmount] 
)
2017, EUR, 1000000  
2018, EUR, 1000000  
2019, EUR, 1000000  
2020, EUR, 1000000  
2017, USD, 500000
2018, USD, 500000
2019, HKD, 1000000
2020, HKD, 1000000

This all works fine so far.

However, I have a reporting requirement to total for each currency. However, the total should not include the current year (which I can pass in from a parameter if needed)

To replicate what the current SQL based report already does, Ideally I'd also like to add an order by sequence to the result set, in order to format the report. The end result set would look like this:

1, 2017, EUR, 1000000  
2, 2018, EUR, 1000000  
3, 2019, EUR, 1000000  
4, Total, EUR, 4000000 
5, 2020, EUR, 1000000  
 
1, 2017, USD, 500000    
2, 2018, USD, 500000  
3, Total, USD, 1000000  

1, 2019, HKD, 1000000  
2, Total HKD, 1000000  
3, 2020, HKD, 1000000

The SQL which does this is quite convoluted, so I imagine this might not be possible in DAX. Has anyone any suggestions on examples like this?

Upvotes: 0

Views: 157

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40244

This would get you the total rows as a new calculated table:

Totals =
VAR CurrYear =
    YEAR ( TODAY () )
RETURN
    SUMMARIZECOLUMNS (
        'Currency'[Currency],
        "SalesAmount", CALCULATE ( [SalesAmount], 'Years'[Year] < CurrYear ),
        "Index", CALCULATE ( COUNT ( 'Years'[Year] ), 'Years'[Year] < CurrYear ) + 1
    )

I bet you can figure out how to union this with the table you've already to get what the combined table you want.

Upvotes: 1

ser_nicky
ser_nicky

Reputation: 356

You can try something like this:

Cumulative =
VAR currYear =
    SELECTEDVALUE ( 'Table'[Date].[Year] )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date].[Year] < currYear ),
        'Table'[Value]
    )

Upvotes: 0

Related Questions