Forum Discussion
DAX - Running Total YTD
chreds The relationship is needed for measure and time intelligence.
Yes you can create as a calculated column. You calculate the sum of all the previous rows/dates
Cumulative = VAR RowDate = Table1[Date] RETURN CALCULATE ( SUM ( Table1[Recurring] ); FILTER ( Table1; Table1[Date] <= RowDate && YEAR ( Table1[Date] ) = YEAR ( RowDate ) ) )You can use or delete
&& YEAR ( Table1[Date] ) = YEAR ( RowDate )
if you need YTD or Cumulative Life to date
Thanks austinsense, konstantinos . That's great info. I've setup a DatesTable now.
Is there a way to do this as a Calculated Column instead of a Measure? I have this working as a measure now but I'd like to be able to work with the information as a column instead of this just being available at the report level.
1.
CALCULATE(SUM(Table[Recurring]), DATESYTD(DatesTable[Date]))
and
2.
Cumulative = CALCULATE(SUM(Table[Recurring]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date]) && DateTable[Year] = YEAR(MAX(DateTable[Date])) ))
Both are just calculating the same value as in the Recurring column and not the cumulative total for that year.
What should the relationship be between the two tables?
My tables look like this now:
Table:
Date Recurring Cumulative (Calculated Column) 2016-01-01 0:00 0 0 2016-02-01 0:00 5 5 2016-03-01 0:00 5 5 2016-04-01 0:00 5 5 2016-05-01 0:00 5 5 2016-06-01 0:00 5 5 2016-07-01 0:00 5 5 2016-08-01 0:00 5 5 2016-09-01 0:00 5 5 2016-10-01 0:00 5 5 2016-11-01 0:00 5 5 2016-12-01 0:00 5 5 2017-01-01 0:00 5 5
DateTable:
Date Year 2016-01-01 0:00 2016 2016-02-01 0:00 2016 2016-03-01 0:00 2016 2016-04-01 0:00 2016 2016-05-01 0:00 2016 2016-06-01 0:00 2016 2016-07-01 0:00 2016 2016-08-01 0:00 2016 2016-09-01 0:00 2016 2016-10-01 0:00 2016 2016-11-01 0:00 2016 2016-12-01 0:00 2016 2017-01-01 0:00 2017
chreds The relationship is needed for measure and time intelligence.
Yes you can create as a calculated column. You calculate the sum of all the previous rows/dates
Cumulative =
VAR RowDate = Table1[Date]
RETURN
CALCULATE (
SUM ( Table1[Recurring] );
FILTER (
Table1;
Table1[Date] <= RowDate
&& YEAR ( Table1[Date] ) = YEAR ( RowDate )
)
)You can use or delete
&& YEAR ( Table1[Date] ) = YEAR ( RowDate )
if you need YTD or Cumulative Life to date
My requirement is to display the cumulative sum for Actual and Scheduled Quantity from the start of the current month to the current day of the current month. Scheduled Quantity would be for the entire current month.
Current Month is Feb so X-axis would have days from 1 to 28. Y-Axis would show both cumulative sum of actual and scheduled quantity.
The actual and scheduled quantity is from the same table which also contains date key. The date key is common between this table and the date table.
Cumulative Scheduled Quantity = CALCULATE(SUM(F_PROJECT_PROGRESS_WORKMEN[Scheduled_QTY]),FILTER(ALL(D_DATE[Date_Key]),D_DATE[Date_Key]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && D_DATE[Date_Key]<=[Today's Date]))
Cumulative Actual Quantity = CALCULATE(SUM(F_PROJECT_PROGRESS_WORKMEN[Actual_QTY]),FILTER(ALL(D_DATE[Date_Key]),D_DATE[Date_Key]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && D_DATE[Date_Key]<=[Today's Date]))
These are the measures I created as I can't create Columns in Direct query mode unless I am misinformed on something here.
Please help me with the same as I need to turn this around by today. Thanks in advance.
NIV YTD = CALCULATE(SUM(RO_01ViewSalesSplit[NIV]),FILTER(RO_01ViewSalesSplit,RO_01ViewSalesSplit[Luna]<=8)
However the displayed values are still at month level and I do not get a Runing Total
Luna NIV NIV YTD
1 15323061.53 15323061.54
2 17546153.57 17546153.57
3 21148304.41 21148304.41
4 16476112.20 16476112.20
5 17277334.76 17277334.76
6 17576768.19 17576768.19
7 19528658.91 19528658.91
8 20691773.40 20691773.40
