Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.