Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerrdl
Helper I
Helper I

Quick Measure YoY%

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

1 ACCEPTED SOLUTION

Thanks a lot for the response. And thanks for introducing to DAX Studio 🙂

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur ,

Could you please share the solution link again? The one you attached is not available now.

Thank you

 

Hi,

See the attached file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

***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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ibarrau
Super User
Super User

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]


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Thanks a lot for the response. And thanks for introducing to DAX Studio 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.