Sum to Date

[NathanSaber]

I'm having trouble calculating the Sum to Date when selecting a month from a Slicer.

The result I'm expecting is per table below when I select Feb from the Month Slicer

Category	Month	To Date
A	30	80
B	100	135

I've tried the following DAX formula for my To Date Measure, however it is returning the same results as the Month column above.

To Date =

CALCULATE(SUM(Transactions[Amount]),FILTER(ALL(Transactions[Period]),Transactions[Period] <= MAX(Transactions[Period])))

The details of the tables are below. They are linked via the Period field in both Tables. One to Many relationship (One - Period Table, Many - Transactions Table)

Transactions

Period	Amount	Category
1	20	A
1	30	B
4	20	A
4	10	A
4	5	B
8	40	B
8	30	A
8	60	B
9	15	A
9	25	B
12	10	A
12	30	A

Period

Period	Month
1	Jul
2	Aug
3	Sep
4	Oct
5	Nov
6	Dec
7	Jan
8	Feb
9	Mar
10	Apr
11	May
12	Jun

[Anonymous]

Hi @NathanSaber,

You can add a values function to your formula to simple group the rolling result based on the current category:

To Date =
CALCULATE (
    SUM ( Transactions[Amount] ),
    FILTER (
        ALLSELECTED ( Transactions[Period] ),
        Transactions[Period] <= MAX ( Transactions[Period] )
    ),
    VALUES ( Transactions[Category] )
)

DAX – The Many Faces of VALUES() 

BTW, I'd like to suggest you use allselected to replace all function if you want to keep original filter effects apply on your table:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 
Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@NathanSaber , In case you have date prefer Time Intelligence

In This case, the Assume period is joined to Transaction. Ideally, In such cases you need to have the time/period table separately and use that

To Date =
CALCULATE(SUM(Transactions[Amount]),FILTER(ALL(Period),Period[Period] <= MAX(Period[Period])))

In case you have a Date, refer

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

[Greg_Deckler]

@NathanSaber - Try

Measure =
  VAR __Category = MAX('Transactions'[Category])
  VAR __Period = MAX('Period'[Period])
RETURN
  SUMX(FILTER(ALL('Transactions'),[Category]=__Category && [Period]<=__Period),[Amount])