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.

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