Forum Discussion

Gregs138's avatar
Gregs138
Icon for Helper I rankHelper I
9 years ago
Solved

DAX Question on YTD Totals

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...
Need Help
  • Gregs138's avatar
    Gregs138
    9 years ago

    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's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

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's avatar
Gregs138
Icon for Helper I rankHelper I
9 years ago

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!