Forum Discussion

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

Count dates for the month and accumulate values

Hi,   I have a table looks like below:       I need to create a graph that will show the cumulative value of sales in each month of the year - X axis (... 12.2018, 01.2019, 02.2019, 03.20...
  • dedelman_clng's avatar
    6 years ago

    Hi pawelk3  - 

     

    Start by creating a Date table

    DateTab = ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR([Date]), "Month", MONTH([Date]))

     

    Make a relationship between the date table and your data

     

     

    Create a new measure Cumulative Sales

     

    Cumulative Sales = TOTALYTD(COUNT(Products[ContractDate]), DateTab[Date])

     

    This will automatically recalculate the total if a Group is selected

    All:

     

    Group G1

     

     

    Hope this helps

    David

  • dedelman_clng's avatar
    dedelman_clng
    6 years ago

    Oops Sorry!  🙁  Put a parenthesis in the wrong place (remove the one directly after the first instance of DateTab[Date] on the last row, move it to the end of that row).

     

    Cumulative Sales =
    CALCULATE (
        COUNT ( Products[ContractDate] ),
        FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
    )