dax for calculating sum PTD based on different columns

[Anonymous]

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

[edhans · Accepted 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
    )
)

[Icey]

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 · Accepted solution]

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

[edhans · Accepted 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
    )
)