Forum Discussion

renlaforest's avatar
renlaforest
Helper I
4 years ago
Solved

Cumulative Daily Target

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's avatar
    tamerj1
    4 years ago

    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

17 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      renlaforest
      Helper I
      4 years ago

      Hi tamerj1 ,

       

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

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        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 )
    )
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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