Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Incremental Measure

Hi All,

 

I want to create a measure where I want to show the incremental sum of the last 6 weeks. 
For example the values for 05/18 and 05/25 should show just 1 ( 289-288) and rest should show 0 ( 288-288).
Any help how to achieve this?

 

So basically instead of the total sum I want to show the incremental sum her in the measure

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 

    I create a sample table:

     

    Then create a new measure and try the follow DAX expression:

     

     

    Incremental_Assessment = 
VAR CurrentWeek = MAX('Table'[Date])
VAR PreviousWeek = CALCULATE(MAX('Table'[Date]), DATEADD('Table'[Date], -7, DAY))
VAR CurrentWeekSum = CALCULATE(SUM('Table'[Total Assessments]), 'Table'[Date] = CurrentWeek)
VAR PreviousWeekSum = CALCULATE(SUM('Table'[Total Assessments]), 'Table'[Date] = PreviousWeek)
RETURN
IF(CurrentWeekSum > PreviousWeekSum, CurrentWeekSum - PreviousWeekSum, 0)

     

     

     

    Here is my preview:

     

    If you want to show the last 6 weeks, you can use the filter or slicer to filter it.

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    I create a sample table:

     

    Then create a new measure and try the follow DAX expression:

     

     

    Incremental_Assessment = 
VAR CurrentWeek = MAX('Table'[Date])
VAR PreviousWeek = CALCULATE(MAX('Table'[Date]), DATEADD('Table'[Date], -7, DAY))
VAR CurrentWeekSum = CALCULATE(SUM('Table'[Total Assessments]), 'Table'[Date] = CurrentWeek)
VAR PreviousWeekSum = CALCULATE(SUM('Table'[Total Assessments]), 'Table'[Date] = PreviousWeek)
RETURN
IF(CurrentWeekSum > PreviousWeekSum, CurrentWeekSum - PreviousWeekSum, 0)

     

     

     

    Here is my preview:

     

    If you want to show the last 6 weeks, you can use the filter or slicer to filter it.

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks Anonymous for the update

      I actually wanted the incremental to run like current week compared to the 6th week in past so I tweeked the measure a bit and I think I've got my output . 
      I wanted to subtract my current week count with the count from 6th Week in the past ( Currentweek - 6WeekAgo count)

       



  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Instead of addition can I get subtraction here?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I want something like below , the last column


    The below measure is still not working,
    any urgent help here?

    IncrementalCountLast6Weeks =
    VAR CurrentWeek = WEEKNUM(TODAY())
    VAR Last6Weeks =
        FILTER(
            VALUES(DateTable[WeekNumber]),
            DateTable[WeekNumber] >= CurrentWeek - 5 && DateTable[WeekNumber] <= CurrentWeek
        )
    RETURN
    CALCULATE(
        [WeeklyAssessmentCount1],
        Last6Weeks
    )