topic Re: Amend a running total to allow it to be filtered by date in DAX Commands and Tips

Amend a running total to allow it to be filtered by date

walter_564 — Thu, 06 Oct 2022 03:02:15 GMT

Hi,

I have inherited a power BI file which includes a slide that has the amount of time for customers to be serviced on the x axis as a total of minutes taken. On the y axis is the count of customers going up to 100% serviced & the line is a running total.

I would like to filter the line by the day of the week so that if there is a poor performance day it can be ommited from the data. The current DAX for the running total is as below

runsum_% =

runsum_lastweek = CALCULATE( SUM('TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] ) && 'TTT - Last Week'[Turn Time Minutes] <= MAX('TTT - Last Week'[Turn Time Minutes]) )) / CALCULATE( SUM( 'TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] )))

When i add in a filter it does not create a new total of 100% (1.0) i can see this when a test it by viewing it as a table

I have read various post on here about using filter(all but it doesnt quite seem to fit with the DAX calculation above . I have tried making a simple table which just contains the relevant 7 days of the week with the below formula:

Last week Days Table = VALUES('TTT - Last Week'[Visit Start Date])

And then tried using VAR to use the filter(all function

RUNSUM = CALCULATE( SUM('TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] ) && 'TTT - Last Week'[Turn Time Minutes] <= MAX('TTT - Last Week'[Turn Time Minutes]) ))

VAR

RUNSUM2 = CALCULATE( SUM( 'TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] )))

RETURN

CALCULATE(DIVIDE(RUNSUM, RUNSUM2), filter(ALL('Last week Days Table', 'Last week Days Table'[Visit Start Date]), 'Last week Days Table'[Visit Start Date] >= MAX('Last week Days Table'[Visit Start Date])

))

and had no luck. Data sample below:

Many thanks

Re: Amend a running total to allow it to be filtered by date

v-xiaotang — Fri, 07 Oct 2022 09:51:34 GMT

Hi walter_564

Thanks for reaching out to us.

could you share a sample .pbix file that fully covers your question? thanks

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Re: Amend a running total to allow it to be filtered by date

walter_564 — Sun, 09 Oct 2022 19:49:17 GMT

Hello,

Thanks for your reply, yes as below
https://drive.google.com/file/d/1HYm25xNsrgBoDgNWMCnKWX9pvuH7ndC5/view?usp=sharing

Re: Amend a running total to allow it to be filtered by date

walter_564 — Tue, 11 Oct 2022 21:10:15 GMT

If it assists below is the sample data in table format - however with reduced rows to allow for the 20k character limit.
I have progressed as far as the below calculation but found when the information is filtered, i no longer get a total of 1

runsum_lastweek3 =

IF(HASONEVALUE('Last week Days Table'[Visit Start Date]),

CALCULATE( SUM('TTT - Last Week'[Count Trucks]) , FILTER( ALL( 'TTT - Last Week' ),'TTT - Last Week'[Visit Start Date] <= MAX( 'TTT - Last Week'[Visit Start Date]) && 'TTT - Last Week'[Turn Time Minutes] <= MAX('TTT - Last Week'[Turn Time Minutes]))) / CALCULATE( SUM( 'TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Visit Start Date] <= MAX( 'TTT - Last Week'[Visit Start Date] )))

)

Fin_Year	Cal_Month	Cal Year	Turn Time Minutes	Count Trucks	Visit Start Date
Last Week	9	2022	4	1	27/09/2022 0:00
Last Week	9	2022	4	1	29/09/2022 0:00
Last Week	9	2022	5	1	28/09/2022 0:00
Last Week	9	2022	5	2	30/09/2022 0:00
Last Week	9	2022	6	4	26/09/2022 0:00
Last Week	9	2022	6	1	27/09/2022 0:00
Last Week	9	2022	6	1	28/09/2022 0:00
Last Week	9	2022	6	6	29/09/2022 0:00
Last Week	9	2022	6	4	30/09/2022 0:00
Last Week	9	2022	7	7	26/09/2022 0:00
Last Week	9	2022	7	9	28/09/2022 0:00
Last Week	9	2022	7	4	29/09/2022 0:00
Last Week	9	2022	7	5	30/09/2022 0:00
Last Week	9	2022	8	3	26/09/2022 0:00
Last Week	9	2022	8	3	28/09/2022 0:00
Last Week	9	2022	8	3	29/09/2022 0:00
Last Week	9	2022	8	5	30/09/2022 0:00
Last Week	9	2022	9	6	26/09/2022 0:00
Last Week	9	2022	9	4	28/09/2022 0:00
Last Week	9	2022	9	5	29/09/2022 0:00
Last Week	9	2022	9	5	30/09/2022 0:00
Last Week	9	2022	10	6	26/09/2022 0:00
Last Week	9	2022	10	1	27/09/2022 0:00
Last Week	9	2022	10	5	28/09/2022 0:00
Last Week	9	2022	10	7	29/09/2022 0:00
Last Week	9	2022	10	6	30/09/2022 0:00
Last Week	9	2022	11	8	26/09/2022 0:00
Last Week	9	2022	11	1	27/09/2022 0:00
Last Week	9	2022	11	2	28/09/2022 0:00
Last Week	9	2022	11	7	29/09/2022 0:00
Last Week	9	2022	11	5	30/09/2022 0:00
Last Week	9	2022	12	4	26/09/2022 0:00
Last Week	9	2022	12	7	28/09/2022 0:00
Last Week	9	2022	12	9	29/09/2022 0:00
Last Week	9	2022	12	4	30/09/2022 0:00
Last Week	9	2022	13	6	26/09/2022 0:00
Last Week	9	2022	13	1	27/09/2022 0:00
Last Week	9	2022	13	7	28/09/2022 0:00
Last Week	9	2022	13	5	29/09/2022 0:00
Last Week	9	2022	13	6	30/09/2022 0:00
Last Week	9	2022	14	7	26/09/2022 0:00
Last Week	9	2022	14	3	27/09/2022 0:00
Last Week	9	2022	14	9	28/09/2022 0:00
Last Week	9	2022	14	5	29/09/2022 0:00
Last Week	9	2022	14	7	30/09/2022 0:00
Last Week	9	2022	15	4	26/09/2022 0:00
Last Week	9	2022	15	2	27/09/2022 0:00
Last Week	9	2022	15	3	28/09/2022 0:00
Last Week	9	2022	15	4	29/09/2022 0:00
Last Week	9	2022	15	10	30/09/2022 0:00
Last Week	9	2022	16	6	26/09/2022 0:00
Last Week	9	2022	16	5	28/09/2022 0:00
Last Week	9	2022	16	10	29/09/2022 0:00
Last Week	9	2022	16	7	30/09/2022 0:00
Last Week	9	2022	17	4	26/09/2022 0:00
Last Week	9	2022	17	1	27/09/2022 0:00
Last Week	9	2022	17	2	28/09/2022 0:00
Last Week	9	2022	17	7	29/09/2022 0:00
Last Week	9	2022	17	8	30/09/2022 0:00
Last Week	9	2022	18	6	26/09/2022 0:00
Last Week	9	2022	18	3	27/09/2022 0:00
Last Week	9	2022	18	4	28/09/2022 0:00
Last Week	9	2022	18	7	29/09/2022 0:00
Last Week	9	2022	18	11	30/09/2022 0:00
Last Week	9	2022	19	4	26/09/2022 0:00
Last Week	9	2022	19	1	27/09/2022 0:00
Last Week	9	2022	19	5	28/09/2022 0:00
Last Week	9	2022	19	5	29/09/2022 0:00
Last Week	9	2022	19	3	30/09/2022 0:00
Last Week	9	2022	20	6	26/09/2022 0:00
Last Week	9	2022	20	3	27/09/2022 0:00
Last Week	9	2022	20	6	28/09/2022 0:00
Last Week	9	2022	20	7	29/09/2022 0:00
Last Week	9	2022	20	6	30/09/2022 0:00
Last Week	9	2022	21	8	26/09/2022 0:00
Last Week	9	2022	21	5	27/09/2022 0:00
Last Week	9	2022	21	4	28/09/2022 0:00
Last Week	9	2022	21	8	29/09/2022 0:00
Last Week	9	2022	21	8	30/09/2022 0:00
Last Week	9	2022	22	5	26/09/2022 0:00
Last Week	9	2022	22	1	27/09/2022 0:00
Last Week	9	2022	22	5	28/09/2022 0:00
Last Week	9	2022	22	3	29/09/2022 0:00
Last Week	9	2022	22	8	30/09/2022 0:00
Last Week	9	2022	23	7	26/09/2022 0:00
Last Week	9	2022	23	1	27/09/2022 0:00
Last Week	9	2022	23	2	28/09/2022 0:00
Last Week	9	2022	23	3	29/09/2022 0:00
Last Week	9	2022	23	5	30/09/2022 0:00
Last Week	9	2022	24	4	26/09/2022 0:00
Last Week	9	2022	24	1	27/09/2022 0:00
Last Week	9	2022	24	7	28/09/2022 0:00
Last Week	9	2022	24	6	29/09/2022 0:00
Last Week	9	2022	24	4	30/09/2022 0:00
Last Week	9	2022	25	7	26/09/2022 0:00
Last Week	9	2022	25	3	28/09/2022 0:00
Last Week	9	2022	25	6	29/09/2022 0:00
Last Week	9	2022	25	2	30/09/2022 0:00
Last Week	9	2022	26	5	26/09/2022 0:00
Last Week	9	2022	26	2	27/09/2022 0:00
Last Week	9	2022	26	2	28/09/2022 0:00
Last Week	9	2022	26	8	30/09/2022 0:00
Last Week	9	2022	27	3	26/09/2022 0:00
Last Week	9	2022	27	2	27/09/2022 0:00
Last Week	9	2022	27	6	28/09/2022 0:00
Last Week	9	2022	27	4	29/09/2022 0:00
Last Week	9	2022	27	5	30/09/2022 0:00
Last Week	9	2022	28	7	26/09/2022 0:00
Last Week	9	2022	28	6	27/09/2022 0:00
Last Week	9	2022	28	6	28/09/2022 0:00
Last Week	9	2022	28	3	29/09/2022 0:00
Last Week	9	2022	28	4	30/09/2022 0:00
Last Week	9	2022	29	8	26/09/2022 0:00
Last Week	9	2022	29	2	28/09/2022 0:00
Last Week	9	2022	29	4	29/09/2022 0:00

Any assistance would be really appreciated - thanks

Re: Amend a running total to allow it to be filtered by date

walter_564 — Mon, 12 Jun 2023 21:33:49 GMT

solved this as below

runsum_%Last_Week =

CALCULATE( SUM( TTT[Count_trucks] ) , FILTER( ALLSELECTED( TTT ) , TTT[FinYear] = MAX( TTT[FinYear] ) && TTT[Turn Time] <= MAX(TTT[Turn Time]) ) )

/ CALCULATE( SUM( TTT[Count_trucks] ) , FILTER( ALLSELECTED( TTT ) , TTT[FinYear] = MAX( TTT[FinYear] ) ) )