DAX Question on YTD Totals

[Gregs138]

So I have tried the following expression to calculate a running/cumulative total without any luck so far.

A quick web search seems to imply this should work but some examples were from a Power Pibot model where they "set the date dimension".

This is a simple fact table joined to a date dimension via dimDayId(which is an integer, there is no actual date type field in the fact table)

Local Period Balance Amount YTD = 
CALCULATE(
sum(factGLBalance[Local Periodic Balance Amt]),'prd dimDate', 'prd dimDate'[Year] = YEAR((now())))

It almost works, but I do not get a running total, the results look like:

Jan 2017   $25.00

Feb 2017   $15.00

March 2017 $30.00

Q1    $70.00

What I need is

Jan 2017   $25.00

Feb 2017  $40.00

March 2017 $70.00

Q1 70

Any suggestions or pointers for Cumulative totals?

[TomMartens]

Hey,

here you will find a little example

I guess that the Measure

Amount YTD = 
CALCULATE(
	SUM(Table1[Amount]),
	FILTER(
		ALL('Calendar'),
		'Calendar'[DateIndex] <= MAX('Table1'[DateIndex]) &&
		'Calendar'[Year] = MAX('Calendar'[Year])
	)
)

creates what you are looking for

Whereas using Year(Now()) could be become difficult to explain to your audience in some reports

[Gregs138 · Accepted solution]

Solution was listed here at the bottom: https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

If you are using a dimDate , then joining on Integer Date will not "Mark the Date Dim as Date Dim"  idea.  You have to create a dummy date table as illustrated towards the middle of that article and setup a join between it and the date dim.  This basically "tricks" the model into thinking that the Date field (not dimDayId)  is the primary key.  

It's sort of a workaround, but it gets it done.

For cumulative totals by month the formulas ended up being:

Local Periodic Balance Amount (measure in the table)  this just gives the monthly total

Local Period Balance Amt Cumulative = TOTALYTD(sum(factGLBalance[Local Periodic Balance Amt]),'prd dimDate'[Date],DATESYTD('prd dimDate'[Date]))      

Local Period Balance Amt PY Cumulative = TOTALYTD(sum(factGLBalance[Local Periodic Balance Amt]),SAMEPERIODLASTYEAR('prd dimDate'[Date]))

Local Period Cumlative YoY Variance = [Local Period Balance Amt Cumulative] - [Local Period Balance Amt PY Cumulative]

Local Period YoY Variance % = DIVIDE( [Local Period Cumlative YoY Variance] ,[Local Period Balance Amt PY Cumulative])

Hope that helps anyone having similar issue!

[TomMartens]

Hey,

I would try something like this

Local Period Balance Amount YTD = 
CALCULATE(
sum(factGLBalance[Local Periodic Balance Amt]),
Filter(
ALL('prd dimDate'),
'prd dimDate'[integerColumn] <= max('prd dimDate'[integerColumn] )
&& 'prd dimDate'[Year] = YEAR(now())
)
)

[TomMartens]

hey,

let me direct your attention to this site:

http://www.daxpatterns.com/time-patterns/

hope this helps

[Gregs138]

Thanks Tom, but I have looked at at that example, It is pretty similar to what I have posted and still no dice.

[mattbrice]

Actually, Tom's code is not that similar to what you initially posted and from what info you have provided his solution should work.  If it does not, then I would check to make sure you have don't have other filters causing issues.