topic Re: Cumulative Daily Target in DAX Commands and Tips

Cumulative Daily Target

renlaforest — Wed, 04 May 2022 15:07:12 GMT

Hi - It's embarassing how many hours I've spent trying to figure this out.

My Data Model has a table called Project Setup with project in each row. Each project has a column called Start Date, Close Date, Total Days (close day -start date)+1, and DailyENTarget - the target is different for each project on the list.

My table has the daily target listed - this project has a target of 3288 (about 235 per day) and closes on May 11th:

The dates are based on a table/column called EnumDates[Date] which has a relationship with the other table.

I'd like the ENTargetToDate column to show the cumulative target for each day based on the target that's in the data model table and grows incrementaly each day. I have not been able to figure out how to accomplish this.

Re: Cumulative Daily Target

Greg_Deckler — Wed, 04 May 2022 15:31:19 GMT

renlaforest Basically a Running Total measure (there is a Quick Measure for this) but in column form:

ENTargetToDate column = VAR __Project = 'Table'[Project] //assumes you have a project column in your tracking table VAR __DailyENTarget = MAXX(FILTER('Projects',[Project] = __Project),[DailyENTarget) VAR __Date = [Date] VAR __Days = COUNTROWS(FILTER(ALL('Table'),[Date] <= __Date)) RETURN __Days * __DailyENTarget

Re: Cumulative Daily Target

renlaforest — Wed, 04 May 2022 16:27:14 GMT

Greg_DecklerThank you! I'm down to just one issue (I think):

Re: Cumulative Daily Target

tamerj1 — Wed, 04 May 2022 18:16:21 GMT

Hi renlaforest

you may try

ENTargetToDate = VAR CrrentDate = MAX ( EnumDates[Date] ) RETURN CALCULATE ( SUM ( 'ProjectSetup'[DailyENTarget] ), ALLEXCEPT ( 'ProjectSetup', 'ProjectSetup'[ProjectName] ), FILTER ( EnumDates, EnumDates[Date] >= CrrentDate ) )

Re: Cumulative Daily Target

renlaforest — Wed, 04 May 2022 18:59:46 GMT

Hi tamerj1 ,

Thank you - I'm still ending up with the same result. I really appreciate your help:

Re: Cumulative Daily Target

tamerj1 — Wed, 04 May 2022 19:15:30 GMT

renlaforest

I guess I missed something

ENTargetToDate = VAR CrrentDate = MAX ( EnumDates[Date] ) RETURN CALCULATE ( SUM ( 'ProjectSetup'[DailyENTarget] ), ALLEXCEPT ( 'ProjectSetup', 'ProjectSetup'[ProjectName] ), REMOVEFILTERS ( EnumDates ), FILTER ( EnumDates, EnumDates[Date] >= CrrentDate ) )

Re: Cumulative Daily Target

renlaforest — Thu, 05 May 2022 13:22:00 GMT

Thank you tamerj1 ,

Unfortunately, I still get the same result.

Re: Cumulative Daily Target

tamerj1 — Thu, 05 May 2022 13:29:30 GMT

Can you please share a sample file?

Re: Cumulative Daily Target

tamerj1 — Thu, 05 May 2022 13:33:12 GMT

renlaforest
Please try this just to see what results you get

ENTargetToDate = VAR CrrentDate = MAX ( EnumDates[Date] ) RETURN CALCULATE ( SUM ( 'ProjectSetup'[DailyENTarget] ), REMOVEFILTERS (), EnumDates[Date] <= CrrentDate )

Re: Cumulative Daily Target

renlaforest — Thu, 05 May 2022 13:36:45 GMT

Hi tamerj1 ,

It's now showing the total for 6 days (6*235) - although there are 7 days listed:

Re: Cumulative Daily Target

tamerj1 — Thu, 05 May 2022 13:40:18 GMT

renlaforest
Ok Then try

ENTargetToDate = VAR CrrentDate = MAX ( EnumDates[Date] ) RETURN CALCULATE ( SUM ( 'ProjectSetup'[DailyENTarget] ), REMOVEFILTERS (), VALUES ( EnumDates[Date] ) EnumDates[Date] <= CrrentDate )

Re: Cumulative Daily Target

renlaforest — Thu, 05 May 2022 13:49:27 GMT

Hitamerj1 - there's an issue with the last EnumDates[Date]

Re: Cumulative Daily Target

renlaforest — Thu, 05 May 2022 13:53:44 GMT

Hi tamerj1 - I thought there may be a missing comma, so I added this one, which resulted in:

Re: Cumulative Daily Target

tamerj1 — Thu, 05 May 2022 14:01:50 GMT

ENTargetToDate = VAR CrrentDate = MAX ( EnumDates[Date] ) RETURN CALCULATE ( SUM ( 'ProjectSetup'[DailyENTarget] ), REMOVEFILTERS (), VALUES ( EnumDates[Date] ), EnumDates[Date] <= CrrentDate )

Re: Cumulative Daily Target

renlaforest — Thu, 05 May 2022 16:42:23 GMT

tamerj1, here is the result:

Thank you so much for your help with this.

Re: Cumulative Daily Target

tamerj1 — Thu, 05 May 2022 16:44:23 GMT

renlaforest

Please let me know if you can share sample file?

Re: Cumulative Daily Target

renlaforest — Thu, 05 May 2022 19:59:18 GMT

Hi tamerj1 - sorry it took so long. I think this should work.

https://drive.google.com/file/d/1h24vWpK5mREvPzG1bDXkN94h_5s6qQlX/view?usp=sharing

Re: Cumulative Daily Target

tamerj1 — Fri, 06 May 2022 04:11:48 GMT

Hi renlaforest
Here is the sample file with the solution https://we.tl/t-pHo4kYyRle

ENTargetToDate = VAR CrrentDate = MAX ( EnumDates[Date] ) VAR FirstDateInFilter = CALCULATE ( MIN ( EnumDates[Date] ), ALLSELECTED () ) VAR NumberOfDays = DATEDIFF ( FirstDateInFilter, CrrentDate, DAY ) + 1 VAR ProjectTarget = SELECTEDVALUE ( 'ProjectSetup'[DailyENTarget] ) RETURN ProjectTarget * NumberOfDays