Forum Discussion
Sum to Date
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 =
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 |
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
3 Replies
NathanSaber - Try
Measure = VAR __Category = MAX('Transactions'[Category]) VAR __Period = MAX('Period'[Period]) RETURN SUMX(FILTER(ALL('Transactions'),[Category]=__Category && [Period]<=__Period),[Amount])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-e3174b39f38aHi 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
