Forum Discussion

Big_Trucks's avatar
Big_Trucks
Frequent Visitor
6 years ago
Solved

Cumulative Column Summation

Hi All,   I am having trouble getting my Dax to calculate for what I need. The values need to be in a column, in order to line up with other items in my table for filter calculations. I have tried ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Big_Trucks - 
    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

     

    That said, you might try this:

    ***Quarterly SUM Expected Cumulative = 
VAR __Dates = FILTER(ALL('Date Table'),'Date Table'[Year QTR] <= MAX('Date Table'[Year QTR]))
SUMX (
    SUMMARIZE (
        FILTER('Table1',[Date] IN __Dates),
        'Table1'[** Budget Utilization]
    ),
    [** Quarterly Budget]
)
amitchandak's avatar
amitchandak
Super User
6 years ago

Big_Trucks ,

Unless you want a reset at qtr it should work like this date example

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

 

for QTD

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

 

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.