Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
@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
@NathanSaber - Try
Measure =
VAR __Category = MAX('Transactions'[Category])
VAR __Period = MAX('Period'[Period])
RETURN
SUMX(FILTER(ALL('Transactions'),[Category]=__Category && [Period]<=__Period),[Amount])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |