Use member of a calculation group to calculate a new member (e.g., YTD and YTD previous year)

[E__]

Hi,

I've been experimenting with calculation groups, and I was wondering wether it is possible to use a member of a calculation group to calculate a new member. For example, when calculating YTD and YTD previous year (PY), I normally would use the following formulas:

Sales YTD = TOTALYTD(Sum('Sales'[Revenue]),'Date'[Date])

Sales LY YTD = CALCULATE ([Sales YTD], SAMEPERIODLASTYEAR ('Date'[Date]))

But I'm not sure how to use the same logic when using calculation groups. Is there a way to reference a member of a calculation group (in this case, YTD) when calculating a new member (YTD PY)? 

Thanks in advance!

Best,

Eva

[amitchandak · Accepted solution]

@E__ , Make sure Date/calendar table is marked as "Date" Table. Tight click on the table in Field pane, there is an option mark as date table

You can also try these options

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[Anonymous · Accepted solution]

Calculations groups are a tricky subject and you should be very advanced in DAX to touch it. Yes, you can use members of some groups in calculation members of other groups but you have to be VERY CAREFUL and test thoroughly (you need more testing than usual). You also have to have a clear understanding of what calculation group precedence is and how to properly manage it. If you start mixing members within members you'll be creating a lot of complexity and calculation overhead. After some time you'll be lost. Please read the articles about calculation groups on www.sqlbi.com to know what you're trying to tame. It's a BEAST.

Best
D

[E__]

Thanks for the advice. I've now put more time into understanding calculation groups, and followed SQLBI's "Mastering DAX video course" on calculation groups as well. As you suggested, I won't use recursion. I find it strange that the Microsoft documentation on calculation groups (https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprodu...) (https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprod...) simply mentions that you can use sideways recursion without mentioning any of the "dangers".

Best,

Eva

[amitchandak · Accepted solution]

@E__ , Make sure Date/calendar table is marked as "Date" Table. Tight click on the table in Field pane, there is an option mark as date table

You can also try these options

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))