Cumulative Spend

[KasRotodyne]

Hi,

I need a DAX formula to accumulate spend during the year per Vendor.

Okay lets paint the picture.

I have an invoice database. every Invoice has a unique code. with a vendor id connected to it and a processed date etc. 

Is it possible to accumulate this spend. I want to cumulate the spend per vendor over time periods with a slicer.

What Formula should I use?

What I have now

Cumulative Spend =

CALCULATE(

    SUM(T_PurInvoiceMain[BasicCurrNetPrice_VendInvoice]),

        Filter(T_Purinvoice[VendID],

            ALL(T_PurInvoiceMain[FinalProcessedDate],

                  'T_PurInvoiceMain'[FinalProcessedDate] > MAX('T_PurInvoiceMain'[FinalProcessedDate]))))

[v-jingzhang · Accepted solution]

Hi @KasRotodyne 

I recommend that you add a Calendar (Date) table to the model and connect it to the Main table on Date columns. A Date table has continuous unique dates. This can avoid missing data if Main table doesn't have continuous dates for the slicer or axis. 

Then build relationships between tables following the star schema

At last, use the following measure to calculate the cumulative value. I have attached a sample file at bottom for your reference. Hope it helps. 

Cumulative Amount = 
VAR _date = MAX('Calendar'[Date])
RETURN
CALCULATE(SUM(InvoiceMain[Amount]),ALL('Calendar'),'Calendar'[Date]<=_date)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@KasRotodyne , Best to use date table

Cumulative Spend =
CALCULATE(
SUM(T_PurInvoiceMain[BasicCurrNetPrice_VendInvoice]),
Filter(
ALL(Date[Date],
'Date'[Date] <= MAX('Date'[Date]))))

If only year cumulative, then it is ytd

YTD Sales = CALCULATE(T_PurInvoiceMain[BasicCurrNetPrice_VendInvoice])),DATESYTD('Date'[Date],"12/31"))

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.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[KasRotodyne]

It does not work sadly....

Also After Calculate I need a DAX Function