Forum Discussion

sprao's avatar
sprao
Icon for Helper I rankHelper I
6 years ago
Solved

Cumulative Count by Month from another table value

Hi Members, please help me below scenario

I have 3 tables like below image

Now in my graph I want show No.of task by month with cumulative value

Please help me..Advance Thank you.. 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    sprao 
    Try the following measures, I put in the table visual to show you the result because, I don't have your calender table. You can try yourself.

     

    Plan  = CALCULATE(COUNTROWS('Plan Table'),
        FILTER(ALL('Plan Table'),SUMX(FILTER('Plan Table',EARLIER('Plan Table'[Taskfinishdate].[Year])<='Plan Table'[Taskfinishdate].[Year]),COUNTROWS('Plan Table'))),
        FILTER(ALL('Plan Table'),SUMX(FILTER('Plan Table',EARLIER('Plan Table'[Taskfinishdate].[MonthNo])<='Plan Table'[Taskfinishdate].[MonthNo]|| EARLIER('Plan Table'[Taskfinishdate])<='Plan Table'[Taskfinishdate]),COUNTROWS('Plan Table'))),
        FILTER(ALL('Plan Table'),'Plan Table'[Taskfinishdate]>=RELATED(Datetable[Startdate]) && 'Plan Table'[Taskfinishdate]<=RELATED(Datetable[Enddate])))

     

     

    Baseline = CALCULATE(COUNTROWS('Baseline Table'),
        FILTER(ALL('Baseline Table'),SUMX(FILTER('Baseline Table',EARLIER('Baseline Table'[Baseline_Date].[Year])<='Baseline Table'[Baseline_Date].[Year]),COUNTROWS('Plan Table'))),
        FILTER(ALL('Baseline Table'),SUMX(FILTER('Baseline Table',EARLIER('Baseline Table'[Baseline_Date].[MonthNo])<='Baseline Table'[Baseline_Date].[MonthNo] || EARLIER('Baseline Table'[Baseline_Date])<='Baseline Table'[Baseline_Date]),COUNTROWS('Baseline Table'))),
        FILTER(ALL('Baseline Table'),'Baseline Table'[Baseline_Date]>=RELATED(Datetable[Startdate]) && 'Baseline Table'[Baseline_Date]<=RELATED(Datetable[Enddate])))

     

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    You need open task or cumulative task. The open task is based on the end date. And we need the task start date. Also Task ID

    • sprao's avatar
      sprao
      Icon for Helper I rankHelper I
      6 years ago

      I need cumulative No.of tasks(Plan & Baseline),during the projects period..

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    sprao 
    Try the following measures, I put in the table visual to show you the result because, I don't have your calender table. You can try yourself.

     

    Plan  = CALCULATE(COUNTROWS('Plan Table'),
        FILTER(ALL('Plan Table'),SUMX(FILTER('Plan Table',EARLIER('Plan Table'[Taskfinishdate].[Year])<='Plan Table'[Taskfinishdate].[Year]),COUNTROWS('Plan Table'))),
        FILTER(ALL('Plan Table'),SUMX(FILTER('Plan Table',EARLIER('Plan Table'[Taskfinishdate].[MonthNo])<='Plan Table'[Taskfinishdate].[MonthNo]|| EARLIER('Plan Table'[Taskfinishdate])<='Plan Table'[Taskfinishdate]),COUNTROWS('Plan Table'))),
        FILTER(ALL('Plan Table'),'Plan Table'[Taskfinishdate]>=RELATED(Datetable[Startdate]) && 'Plan Table'[Taskfinishdate]<=RELATED(Datetable[Enddate])))

     

     

    Baseline = CALCULATE(COUNTROWS('Baseline Table'),
        FILTER(ALL('Baseline Table'),SUMX(FILTER('Baseline Table',EARLIER('Baseline Table'[Baseline_Date].[Year])<='Baseline Table'[Baseline_Date].[Year]),COUNTROWS('Plan Table'))),
        FILTER(ALL('Baseline Table'),SUMX(FILTER('Baseline Table',EARLIER('Baseline Table'[Baseline_Date].[MonthNo])<='Baseline Table'[Baseline_Date].[MonthNo] || EARLIER('Baseline Table'[Baseline_Date])<='Baseline Table'[Baseline_Date]),COUNTROWS('Baseline Table'))),
        FILTER(ALL('Baseline Table'),'Baseline Table'[Baseline_Date]>=RELATED(Datetable[Startdate]) && 'Baseline Table'[Baseline_Date]<=RELATED(Datetable[Enddate])))

     

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