Running total where the totals accumulate by both Date and Group

[Lenin]

Hi All,

          I am new to Power BI and am hoping someone can provide assistance. I want to create running totals that are based on both dates and group(s). The first four columns is the test data (TaskTimePhaseDataSet table) I am working from. And the final two columns are my expected calculations. After reviewing many posts and blogs I am not able to get the desired results. Below is the DAX expression I finished on (to get accumulated Task actual work values - column 5 in the table below) but it is not correct and I have reached my DAX (knowledge) limit.

 Cumulative Task Actual Work=CALCULATE (     SUM (TaskTimephasedDataSet[TaskActualWork]),     FILTER (         ALL (TaskTimePhasedDataSet),    TaskTimePhasedDataSet[TimeByDay] <= MAX(TaskTimePhasedDataSet[TimeByDay]) && TaskTimePhasedDataSet[TaskName] <= TaskTimePhasedDataSet[TaskName] )   )

Ideally I would like

1. A working expression
2. Explanation why my original effort does not work.

Table is TaskTimePhaseDataSet

Project Name	TaskName	TimeByDay	TaskActualWork	Cumulative Task Actual Work	Cumulative Project Actual Work
Project 1	Task 1	18/03/2017	4	4	4
Project 1	Task 1	19/03/2017	5	9	9
Project 1	Task 1	20/03/2017	3	12	12
Project 1	Task 2	23/03/2017	2	2	14
Project 1	Task 2	25/03/2017	3	5	17
Project 1	Task 2	26/03/2017	4	9	21
Project 2	Task 1	18/03/2017	7	7	7
Project 2	Task 1	24/03/2017	8	15	15
Project 2	Task 1	27/03/2017	8	23	23
Project 2	Task 3	29/03/2017	7	7	30
Project 2	Task 3	29/03/2017	4	11	34
Project 2	Task 3	30/03/2017	5	16	39

Any help greatly appreciated.

[v-qiuyu-msft · Accepted solution]

Hi @Lenin,

You can create measures like below:

Cumulative Task Actual Work = CALCULATE(SUM(Table2[TaskActualWork]),FILTER(ALL(Table2),'Table2'[TimeByDay]<=MAX('Table2'[TimeByDay])),VALUES(Table2[Project Name]),VALUES(Table2[TaskName]))

Cumulative Project Actual Work = CALCULATE(SUM(Table2[TaskActualWork]),FILTER(ALL(Table2),'Table2'[TimeByDay]<=MAX('Table2'[TimeByDay])),VALUES(Table2[Project Name]))

Best Regards,
Qiuyun Yu

[anakoom]

I have similar  situation and if you can help me with this :

i wants to calculate new column in above D . 

based on todays date

thanks

[Lenin]

Qiuyun, thanks so muck for the help. It works perfectly.

Lenin