Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

dax for calculating sum PTD based on different columns

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. 

 

SiteMonthWeekSalesPTD
AJAN159
AJAN149
AJAN2318
AJAN2618
AJAN3422
AJAN4527
BJAN159
BJAN149
BJAN2318
BJAN2618
BJAN3422
BJAN4527
2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.

edhans_0-1609289362505.png

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
    )
)


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

CNENFRNL
Community Champion
Community Champion

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]
)

Screenshot 2020-12-30 073332.png


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!

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

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

CNENFRNL
Community Champion
Community Champion

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]
)

Screenshot 2020-12-30 073332.png


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!

edhans
Super User
Super User

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.

edhans_0-1609289362505.png

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
    )
)


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors