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 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]))))
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.

3 Replies

  • 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

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    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.