Forum Discussion

KasRotodyne's avatar
KasRotodyne
Frequent Visitor
4 years ago
Solved

Cumulative Spend

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 connec...
Need Help
Show and Tell
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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

KasRotodyne's avatar
KasRotodyne
Frequent Visitor
4 years ago

It does not work sadly....

 

Also After Calculate I need a DAX Function