Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
hello Power BI users,
I have a table with columns like below and I would like help to create a new calculate column/measure (PTD). This PTD is based on Site,Month and Week selected.
Site | Month | Week | Sales | PTD |
A | JAN | 1 | 5 | 9 |
A | JAN | 1 | 4 | 9 |
A | JAN | 2 | 3 | 18 |
A | JAN | 2 | 6 | 18 |
A | JAN | 3 | 4 | 22 |
A | JAN | 4 | 5 | 27 |
B | JAN | 1 | 5 | 9 |
B | JAN | 1 | 4 | 9 |
B | JAN | 2 | 3 | 18 |
B | JAN | 2 | 6 | 18 |
B | JAN | 3 | 4 | 22 |
B | JAN | 4 | 5 | 27 |
Solved! Go to Solution.
It cannot be a calculated column. Those never recalculate during use and ignore user selection.
A measure of = SUM(Table[Sales]) will work. If you put the site, Month, and Week in a visual and filter based on those, it will automatically calculate your values. Nothing fancy needed at all.
If you are trying to get a cumulative total, the correct way is to use a date table, but you can do it with your data.
PTD =
VAR varCurrentSite = MAX('Table'[Site])
VAR varCurrentMonth = MAX('Table'[Month])
VAR varCurrentWeek = MAX('Table'[Week])
RETURN
CALCULATE(
SUM('Table'[Sales]),
FILTER(
ALL('Table'),
'Table'[Site] = varCurrentSite
&& 'Table'[Month] = varCurrentMonth
&& 'Table'[Week] <= varCurrentWeek
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi, @Anonymous , you might want to try such a calculated column
PTD =
SUMX (
FILTER (
Table1,
Table1[Site] = EARLIER ( Table1[Site] )
&& Table1[Month] = EARLIER ( Table1[Month] )
&& Table1[Week] <= EARLIER ( Table1[Week] )
),
Table1[Sales]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @Anonymous ,
Please let us know if the replies above are helpful.
If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please give us more details.
Best Regards,
Icey
Hi, @Anonymous , you might want to try such a calculated column
PTD =
SUMX (
FILTER (
Table1,
Table1[Site] = EARLIER ( Table1[Site] )
&& Table1[Month] = EARLIER ( Table1[Month] )
&& Table1[Week] <= EARLIER ( Table1[Week] )
),
Table1[Sales]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
It cannot be a calculated column. Those never recalculate during use and ignore user selection.
A measure of = SUM(Table[Sales]) will work. If you put the site, Month, and Week in a visual and filter based on those, it will automatically calculate your values. Nothing fancy needed at all.
If you are trying to get a cumulative total, the correct way is to use a date table, but you can do it with your data.
PTD =
VAR varCurrentSite = MAX('Table'[Site])
VAR varCurrentMonth = MAX('Table'[Month])
VAR varCurrentWeek = MAX('Table'[Week])
RETURN
CALCULATE(
SUM('Table'[Sales]),
FILTER(
ALL('Table'),
'Table'[Site] = varCurrentSite
&& 'Table'[Month] = varCurrentMonth
&& 'Table'[Week] <= varCurrentWeek
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCheck out the July 2025 Power BI update to learn about new features.