cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Sum per month

Hi, 

 

I have the following table:

 Sum_per_month_1.JPG

 

I would like to get the sum of the values in the graph instead of the values, so for February it should be 7 and for March it would be 9.

Any tips here?

Sum_per_month_3.JPG

Thanks for any help. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Do you have a dedicated Date Table?  If so, you can leverage the builtin time-intelligence functions to give you Total Year to Date of a measure:

 TOTALYTD MEASURE=
/*-------------------------------------------------------------------------
            'Simple calculation when do not need further complex filtering'
            'Use the Date/Calendar Table and Date Key'
            'Be sure that the Date Table is set as a date table'
--------------------------------------------------------------------------*/
 TOTALYTD ( [MEASURE] , Date[DateKey] ',Optional Fiscal Year End')

If you want to use a column, this code will work:

Cumulative Total Based on Date = 
VAR __CurrentDate= Table1[Date]
RETURN

CALCULATE(
    COUNTROWS( Table1 ),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date]
    )
)
 

Cumualitve Total Calc Column.png

View solution in original post

8 REPLIES 8
jsh121988
Microsoft
Microsoft

I'm not sure where you're getting your example output numbers. You may just need to select Count instead of Distinct Count from the values dropdown.

 

A measure would be better:

MyMeasure = // Remove the // from one of the below based on your need
// COUNTROWS(MyTable) // Use this to sum 1 for each row.
// DISTINCTCOUNT(MyTable[AccountId]) // Use this to sum 1 for each unique Id
// SUM(MyTable[SomeNumberField]) // Use this to sum a particular field

Drag the measure to the values section on the visual.

Anonymous
Not applicable

Thanks for your response. So there are 2 values for January. And then 5 values for February. I would then like the total for February to be 2 + 5. For March there are also 2 values, the total in March should be 2 + 5 +2. 

I tried what you suggested but it doesn't give me required result. 

Example: 7 in total for February and then 9 in total for March.

 

Thank you.

Anonymous
Not applicable

I also tried using the cumulative formula: 

 

Powa01.JPG

Hello,

I've made some modifications to your cumulative formula.  Let me know if this works:

 

Cumulative_Actual =
CALCULATE (
    SUM ( 'Accounts'[accountid] ),
    FILTER (
        ALL ( 'Accounts' ),
        'Accounts'[Date] <= MAX ( 'Accounts'[Date] )
    )
)

Anonymous
Not applicable

Hi, 

 

Thanks Sean but I dont think I have the answer I want. I want to be able to cumulatively add the amounts for the accountid per month. So for January it's 2, for February it should be 7(2 + 5) and for March it would be 9 (2+5+2).

Can you advise what's the best way to get this?

 

Cheers

Anonymous
Not applicable

Do you have a dedicated Date Table?  If so, you can leverage the builtin time-intelligence functions to give you Total Year to Date of a measure:

 TOTALYTD MEASURE=
/*-------------------------------------------------------------------------
            'Simple calculation when do not need further complex filtering'
            'Use the Date/Calendar Table and Date Key'
            'Be sure that the Date Table is set as a date table'
--------------------------------------------------------------------------*/
 TOTALYTD ( [MEASURE] , Date[DateKey] ',Optional Fiscal Year End')

If you want to use a column, this code will work:

Cumulative Total Based on Date = 
VAR __CurrentDate= Table1[Date]
RETURN

CALCULATE(
    COUNTROWS( Table1 ),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date]
    )
)
 

Cumualitve Total Calc Column.png

Anonymous
Not applicable

Hi Nick,

 

That did the job, thank you. I have one more question. I have a column called "order cancelled", it's a date column. 

For example in your table if order 4 was cancelled on February 20, how could you filter this out?

 

Thanks again

Anonymous
Not applicable

This will only continue the count if the date canceled isblank:

Cumulative Total Based on Date = 
VAR __CurrentDate= Table1[Date Ordered]
RETURN

CALCULATE(
    COUNTROWS( Table1 ),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date Ordered]
            && ISBLANK( Table1[Date Cancelled] )
    )
)

Cumualitve Total Calc Column with filters from other columns.png

 

I think that is what you had in mind?

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors