Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
The formula automatically generated by Quick Measure YoY% is as follows with a ".[Date]" attached to the Date Column. This gives wrong result, while I get correct result if I remove the .[Date] part in CALCULATE. What is the difference between using a Date column with .[Date] and without it?
NOP M YoY% =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
VAR __PREV_YEAR =
CALCULATE('Measure'[NOP M], DATEADD('Calendar'[Date].[Date], -1, YEAR))
RETURN
DIVIDE('Measure'[NOP M] - __PREV_YEAR, __PREV_YEAR)
)
Also, what is the technical name for .[Date], .[Month], .[MonthNo] etc. that we get as options when we use any date column in a DAX formula
Solved! Go to Solution.
Thanks a lot for the response. And thanks for introducing to DAX Studio 🙂
Hi-
I'm running into this issue too, where I receive the following message:
Total_Equipment YoY% =
IF(
ISFILTERED('Monthly Eq Trends'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
SUM('Monthly Eq Trends'[Total_Equipment]),
DATEADD('Monthly Eq Trends'[Date].[Date], -15, YEAR)
)
RETURN
DIVIDE(
SUM('Monthly Eq Trends'[Total_Equipment]) - __PREV_YEAR,
__PREV_YEAR
)
)
I tried removing the .[Date] but I'm not having any luck and I am using a table with a Power BI date heiarchy. How can I get this measure to work? Please help! Thanks.
Hi @Anonymous,
Share your data, your actual business question and also show your expected result.
Hi Ashish-
I'm working with mulitple time-series data tables with one master calendar to tie them together. I know that I need to use the default date heirarchy for quick measures to work but I have lost the default date heirarchy. I think the quick measure is having difficulty with the relationships that I have between mulitiple tables. To keep it simple I'll focus on the master date table "Calendar' and one of the data tables.
I generated a master date table with the following code:
Calendar = ADDCOLUMNS (
CALENDAR (DATE(1976,1,1), DATE(2021,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ), "YearQuarter", FORMAT ( [Date], "Q" ) & "Q" & FORMAT ([Date], "YYYY" )
)
Table of monthly sales data of "widgets", that I'd like to have year-over-year growth rates for:
Date | North America Sales | Europe Sales | Japan Sales | ROW Sales | Total Sales |
Jan-78 | $ 210,126,000 | $ 76,815,000 | $ 12,880,000 | $ 18,240,000 | $ 318,061,000 |
Feb-78 | $ 218,159,000 | $ 85,662,000 | $ 15,675,000 | $ 18,388,000 | $ 337,884,000 |
Mar-78 | $ 258,264,000 | $ 105,068,000 | $ 17,728,000 | $ 23,985,000 | $ 405,045,000 |
Apr-78 | $ 235,842,000 | $ 92,800,000 | $ 19,630,000 | $ 20,167,000 | $ 368,439,000 |
May-78 | $ 246,783,000 | $ 95,979,000 | $ 19,219,000 | $ 19,869,000 | $ 381,850,000 |
Jun-78 | $ 290,285,000 | $ 103,225,000 | $ 22,611,000 | $ 23,516,000 | $ 439,637,000 |
Jul-78 | $ 233,536,000 | $ 94,544,000 | $ 22,462,000 | $ 23,434,000 | $ 373,976,000 |
Aug-78 | $ 246,130,000 | $ 83,272,000 | $ 23,302,000 | $ 25,805,000 | $ 378,509,000 |
Sep-78 | $ 306,546,000 | $ 118,699,000 | $ 27,969,000 | $ 28,406,000 | $ 481,620,000 |
Oct-78 | $ 265,833,000 | $ 97,437,000 | $ 23,971,000 | $ 27,685,000 | $ 414,926,000 |
Nov-78 | $ 274,282,000 | $ 104,485,000 | $ 23,435,000 | $ 25,473,000 | $ 427,675,000 |
Dec-78 | $ 337,543,000 | $ 120,704,000 | $ 30,590,000 | $ 33,682,000 | $ 522,519,000 |
Jan-79 | $ 270,417,000 | $ 100,634,000 | $ 23,990,000 | $ 25,499,000 | $ 420,540,000 |
Feb-79 | $ 294,360,000 | $ 107,914,000 | $ 25,294,000 | $ 26,003,000 | $ 453,571,000 |
Mar-79 | $ 348,896,000 | $ 130,615,000 | $ 35,135,000 | $ 32,370,000 | $ 547,016,000 |
Apr-79 | $ 319,159,000 | $ 116,238,000 | $ 29,214,000 | $ 28,927,000 | $ 493,538,000 |
May-79 | $ 340,300,000 | $ 122,380,000 | $ 33,696,000 | $ 34,151,000 | $ 530,527,000 |
Jun-79 | $ 386,522,000 | $ 129,144,000 | $ 37,289,000 | $ 34,972,000 | $ 587,927,000 |
Jul-79 | $ 335,139,000 | $ 115,019,000 | $ 34,903,000 | $ 32,558,000 | $ 517,619,000 |
Aug-79 | $ 350,478,000 | $ 109,873,000 | $ 32,367,000 | $ 32,458,000 | $ 525,176,000 |
Sep-79 | $ 406,120,000 | $ 153,516,000 | $ 39,287,000 | $ 38,359,000 | $ 637,282,000 |
Oct-79 | $ 385,789,000 | $ 146,476,000 | $ 32,805,000 | $ 33,741,000 | $ 598,811,000 |
Nov-79 | $ 379,462,000 | $ 136,923,000 | $ 30,059,000 | $ 36,439,000 | $ 582,883,000 |
Dec-79 | $ 466,989,000 | $ 169,630,000 | $ 40,157,000 | $ 39,931,000 | $ 716,707,000 |
Jan-80 | $ 392,956,000 | $ 262,179,000 | $ 31,882,000 | $ 43,234,000 | $ 730,251,000 |
Feb-80 | $ 411,300,000 | $ 272,646,000 | $ 33,274,000 | $ 44,030,000 | $ 761,250,000 |
Mar-80 | $ 504,200,000 | $ 309,234,000 | $ 38,213,000 | $ 52,401,000 | $ 904,048,000 |
Apr-80 | $ 451,518,000 | $ 284,251,000 | $ 31,867,000 | $ 49,323,000 | $ 816,959,000 |
May-80 | $ 467,605,000 | $ 298,543,000 | $ 35,195,000 | $ 51,582,000 | $ 852,925,000 |
Jun-80 | $ 507,533,000 | $ 315,488,000 | $ 40,166,000 | $ 53,415,000 | $ 916,602,000 |
Jul-80 | $ 443,882,000 | $ 292,194,000 | $ 37,953,000 | $ 48,084,000 | $ 822,113,000 |
Aug-80 | $ 434,960,000 | $ 275,809,000 | $ 34,765,000 | $ 47,819,000 | $ 793,353,000 |
Sep-80 | $ 507,062,000 | $ 318,500,000 | $ 39,212,000 | $ 54,938,000 | $ 919,712,000 |
Oct-80 | $ 437,564,000 | $ 295,256,000 | $ 35,221,000 | $ 50,904,000 | $ 818,945,000 |
Nov-80 | $ 425,870,000 | $ 275,093,000 | $ 32,873,000 | $ 45,751,000 | $ 779,587,000 |
Dec-80 | $ 517,595,000 | $ 303,696,000 | $ 39,250,000 | $ 48,240,000 | $ 908,781,000 |
Jan-81 | $ 393,046,000 | $ 253,081,000 | $ 29,788,000 | $ 39,934,000 | $ 715,849,000 |
Feb-81 | $ 410,033,000 | $ 266,800,000 | $ 32,348,000 | $ 43,840,000 | $ 753,021,000 |
Mar-81 | $ 500,806,000 | $ 281,897,000 | $ 38,990,000 | $ 51,843,000 | $ 873,536,000 |
Apr-81 | $ 419,016,000 | $ 244,517,000 | $ 33,132,000 | $ 45,892,000 | $ 742,557,000 |
May-81 | $ 433,841,000 | $ 241,246,000 | $ 34,375,000 | $ 47,641,000 | $ 757,103,000 |
Jun-81 | $ 506,599,000 | $ 242,903,000 | $ 39,886,000 | $ 50,909,000 | $ 840,297,000 |
Jul-81 | $ 422,720,000 | $ 209,141,000 | $ 31,944,000 | $ 43,855,000 | $ 707,660,000 |
Aug-81 | $ 422,625,000 | $ 187,007,000 | $ 31,260,000 | $ 45,078,000 | $ 685,970,000 |
Sep-81 | $ 532,482,000 | $ 250,062,000 | $ 38,199,000 | $ 52,958,000 | $ 873,701,000 |
Oct-81 | $ 440,180,000 | $ 219,306,000 | $ 33,044,000 | $ 44,071,000 | $ 736,601,000 |
Nov-81 | $ 429,201,000 | $ 228,411,000 | $ 33,874,000 | $ 45,216,000 | $ 736,702,000 |
Dec-81 | $ 512,340,000 | $ 246,667,000 | $ 40,996,000 | $ 52,231,000 | $ 852,234,000 |
Jan-82 | $ 394,358,000 | $ 203,745,000 | $ 28,342,000 | $ 37,521,000 | $ 663,966,000 |
Feb-82 | $ 408,299,000 | $ 230,007,000 | $ 30,738,000 | $ 42,308,000 | $ 711,352,000 |
Mar-82 | $ 521,281,000 | $ 273,084,000 | $ 38,061,000 | $ 50,971,000 | $ 883,397,000 |
Apr-82 | $ 467,258,000 | $ 236,183,000 | $ 32,055,000 | $ 45,544,000 | $ 781,040,000 |
May-82 | $ 474,674,000 | $ 239,299,000 | $ 32,900,000 | $ 46,665,000 | $ 793,538,000 |
Jun-82 | $ 523,760,000 | $ 248,336,000 | $ 38,126,000 | $ 54,405,000 | $ 864,627,000 |
Jul-82 | $ 443,671,000 | $ 201,031,000 | $ 30,719,000 | $ 46,266,000 | $ 721,687,000 |
Aug-82 | $ 450,028,000 | $ 197,921,000 | $ 29,752,000 | $ 45,188,000 | $ 722,889,000 |
Sep-82 | $ 533,488,000 | $ 259,977,000 | $ 38,777,000 | $ 51,165,000 | $ 883,407,000 |
Oct-82 | $ 417,937,000 | $ 219,585,000 | $ 32,473,000 | $ 47,317,000 | $ 717,312,000 |
Nov-82 | $ 434,426,000 | $ 231,788,000 | $ 35,766,000 | $ 43,372,000 | $ 745,352,000 |
Dec-82 | $ 536,395,000 | $ 256,210,000 | $ 40,514,000 | $ 53,234,000 | $ 886,353,000 |
Desired Output would look like:
Year Total Sales Y/Y Growth
1978 4,850,141,000
1979 6,611,597,000 36%
1980 10,024,526,000 52%
1981 9,275,231,000 -7%
1982 9,374,920,000 1%
TIA!
Hi @Anonymous,
You may refer to my solution here.
Hope this helps.
Hello @Ashish_Mathur ,
Could you please share the solution link again? The one you attached is not available now.
Thank you
***Solved****
Yes! Thank you very much Ashish you solved it! I'd mark it solved but I don't see the "Solved" option since this is an offshoot of an initial post.
Hi @Anonymous,
You are welcome. If my reply helped, please mark it as Answer.
Hi powerrdl, when you create the tabular model in power bi after applying changes, Power Bi creates by default a hidden table for each date column in the dataset.
When you write 'Table'[Date] you are using the field on your table, otherwise if you write 'Table'[Date].[Date] you will be using the column in the hidden table.
In order to have a better understanding you can download DAX Studio and load a power bi file with date column. There you will clarify ideas.
Regards,
P/D So strange it work without .[Date]
Happy to help!
Thanks a lot for the response. And thanks for introducing to DAX Studio 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |