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 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?

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!

     

6 Replies

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

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

      • mattbrice's avatar
        mattbrice
        Icon for Solution Sage rankSolution Sage
        9 years ago

        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. 

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    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'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!