Forum Discussion

eryka_90's avatar
eryka_90
Icon for Helper I rankHelper I
2 years ago
Solved

Total Cumulative - Date Hierarchy

Hi,

 

I'm having an issue when to visualize Total cumulative in Metrix table or chart. The total cumulative showing right value when I'm not using date hierarchy (Document Date) but when it's change to date hierarchy, the value showing incorrect. 

# Total Cumulative $ =
CALCULATE(
    [#Total Amount],
    FILTER(
        ALL('Vendor Open Item'[Document Date]),
        'Vendor Open Item'[Document Date] <= MAX('Vendor Open Item'[Document Date])
    )
)
Document Date#Total Amount USD# Total Cumulative $# Open Doc Count# Total Cumulative
10/24/2017 -650-65011
3/8/2018 -670.74-1320.7412
11/30/2018 -1038-2358.7413
12/13/2018 -11394.19-13752.9314
2/15/2019 -1998-15750.9315
5/13/2019 -9828-25578.9316
2/7/2020 325.12-25253.8117
4/3/2020 -564.41-25818.2218
4/15/2020 -1359.67-27177.8919

 

Expected result to visualize as below:

 

Any help/suggestion would be appreciated.

 

TQVM

 

 

 

Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Try this approach

    1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number
    2. Create a relationship (Many to One and Single) from the Date column of the Fact Table to the Date column of the calendar Table
    3. To your visual, drag Date from the Calendar Table
    4. Write this measure

    Measure = calculate([#total amount],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

    Hope this helps.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Try this approach

    1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number
    2. Create a relationship (Many to One and Single) from the Date column of the Fact Table to the Date column of the calendar Table
    3. To your visual, drag Date from the Calendar Table
    4. Write this measure

    Measure = calculate([#total amount],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi eryka_90 

    Please try the following Measure:

    # Total Cumulativeeeeee = 

VAR _select_date = SELECTEDVALUE('Vendor Open Item'[Document Date])
RETURN
CALCULATE(
    SUM('Vendor Open Item'[#Total Amount USD]),
    FILTER(
        ALL('Vendor Open Item'),
        'Vendor Open Item'[Document Date] <= _select_date)
    )

     

     

     

    Result:

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

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