Forum Discussion

jonbox's avatar
jonbox
Icon for Helper II rankHelper II
4 years ago

Cumulative result per month

Hi, i'm trying to create a measure that accumulates the result per month so that when shown in the line graph, taking the total as an example to be 2.0m in may, 11m june, 11.8m in july, 12.3 in august etc as apposed to how it looks currently.

 

Current line graph:

 

Data i'm using:

Actualprediction_USDDate
2.0mApril
9.0mMay
0.8mJune
0.5mJuly
0.8mAugust
0.8mSeptember
0.7October

 

My current measure:

 

total = 
CALCULATE( 
    SUM( eforecastingdata[ActualPrediction_USD]),
    FILTER(
        ALL(eforecastingdata),
        'eforecastingdata'[Date] <= MAX('eforecastingdata'[Date])
    ))

 

 

Need Help

5 Replies

  • truptis's avatar
    truptis
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi jonbox ,

    Create a rank (ID_MONTH) column for sorting it properly by month wise. Then use the below measure:

    Quantity_CUMULATIVE =
    CALCULATE (
    [ActualPrediction_USD,
    FILTER (
    ALL ( Tablename[ID_MONTH] ),
    Tablename[ID_MONTH]
    <= MAX ( Tablename[ID_MONTH] ) - 1
    )

    )

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

    jonbox , You can consider suggestion of truptis . But as you have a date, Create a date table; Join with date of your table and use month year on-axis from date table

     

    Try measure like

     

    total =
    CALCULATE(
    SUM( eforecastingdata[ActualPrediction_USD]),
    FILTER(
    ALL('Date'),
    'Date'[Date] <= MAX('Date'[Date])
    ))

     


    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

     

    • jonbox's avatar
      jonbox
      Icon for Helper II rankHelper II
      4 years ago

      Hi amitchandak,

       

      When you say create a table to organise date and join to existing table, how would i go about doing this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jonbox ,

    Please refer to my pbix file to see if it helps you.

    Create a column firstly.

    Short Month =
SWITCH (
    'Table (2)'[Date],
    "January", 1,
    "Febuary", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12,
    BLANK ()
)

    Then Create a measure.

    Measure = CALCULATE(SUM('Table (2)'[Actualprediction_USD]),FILTER(ALL('Table (2)'),'Table (2)'[Short Month]<=MAX('Table (2)'[Short Month])))

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

     

    Best Regards

    Community Support Team _ Polly

     

    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
    4 years ago

    Hi jonbox ,

    Does that make sense? If so, kindly mark my answer as the solution close the case and help others find the answer  please. Thanks in advance.

     

    Best Regards

    Community Support Team _ Polly

     

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