Forum Discussion

powerrdl's avatar
powerrdl
Helper I
9 years ago
Solved

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

Need Help
Show and Tell
  • powerrdl's avatar
    powerrdl
    9 years ago

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

10 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    9 years ago

    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]

    • powerrdl's avatar
      powerrdl
      Helper I
      9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi Anonymous,

       

      Share your data, your actual business question and also show your expected result.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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!