Total Cumulative - Date Hierarchy

[eryka_90]

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	-650	1	1
3/8/2018	-670.74	-1320.74	1	2
11/30/2018	-1038	-2358.74	1	3
12/13/2018	-11394.19	-13752.93	1	4
2/15/2019	-1998	-15750.93	1	5
5/13/2019	-9828	-25578.93	1	6
2/7/2020	325.12	-25253.81	1	7
4/3/2020	-564.41	-25818.22	1	8
4/15/2020	-1359.67	-27177.89	1	9

Expected result to visualize as below:

Any help/suggestion would be appreciated.

TQVM

[Ashish_Mathur · Accepted solution]

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.

[eryka_90]

Hi @Ashish_Mathur ,

Thank you for your suggestion.

The issue has been resolved.

Regards,

Eryka

[Ashish_Mathur]

You are welcome.

[v-jialongy-msft]

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.