Cumulative Daily Target

[renlaforest]

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.

[tamerj1 · Accepted solution]

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

[tamerj1]

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

[renlaforest]

Hi @tamerj1 ,

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

[tamerj1]

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

[renlaforest]

Thank you @tamerj1 ,

Unfortunately, I still get the same result.

[tamerj1]

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

[renlaforest]

Hi @tamerj1 ,

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

[tamerj1]

@renlaforest 
Ok Then try

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

[renlaforest]

Hi@tamerj1 - there's an issue with the last EnumDates[Date]

[tamerj1]

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

[renlaforest]

@tamerj1, here is the result:

Thank you so much for your help with this.

[tamerj1]

@renlaforest 

Please let me know if you can share sample file?

[renlaforest]

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

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

[renlaforest]

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

[tamerj1]

Can you please share a sample file?

[Greg_Deckler]

@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

  

[renlaforest]

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