Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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))
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
@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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |