Forum Discussion
Running Total - DAX
Hi Matt
I found the solution by inserting an Index column But it`s keeping telling me that there is not enough memory for it ???????????
Seriously ????
The formula is the one behind:
RunTOT = IF(Query1[CalendarYear]= 2017,CALCULATE(SUMX(Query1,Query1[NET SALES]), FILTER(Query1,Query1[Index] > Earlier(Query1[Index]))),CALCULATE(SUMX(Query1,Query1[NET SALES]), FILTER(Query1,Query1[Index] > Earlier(Query1[Index]))))
As indicated in an earlier post, you need to create a date dimension (best to follow our master Marco Russo on SQLBI.com) https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/ and think twice before you accept the option in PowerBI to let PowerBI do this for you.
IMHO you're still thinking in SQL terms and not in DAX terms (and believe me I also had to step through this faze!):
- make sure you have a Date in Query1;
- create a date table (ie dimDate) with at last a Date field and the Year indicator, potentially with a dynamic query or taking the full blown version from our master:
- mark that dimDate table as a date table
- create a relationship between Query1.[Date] and dimDate.[Date]
Then review your measure:
YTD Total =
CALCULATE (
SUM ( Query1.[NET SALES] ),
DATESYTD ( dimDate.[Date] )
)
If your relationship is not active, then you'll need to add the USERELATIONSHIP ( Query1[your_date], dimDate[Date] ), statement the line after SUM(),