locked
Calculate - Filter to the current year without hard coding the year RRS feed

  • Question

  • Hi,

    I'm working on a fairly simple dashboard using a few CUBEVALUE() formulas to display my measures (current year totals of each type of calls). Instead of creating a filter and applying the current year, I'm trying to add a filter to my calculate measure that filters for the current year based on today's date. My thought is if I do it this way, I won't have to go in and changed the "Year" filter of the measures. I can create a filter, apply the CUBEVALUE() formula and hide it, but that seems a bit inefficient. I have several years of data in my data model. I could also just limit the data model to the current year, but I would also have to change that each new year. 

    =CALCULATE(DISTINCTCOUNT(apparatus[incidentkey]),
                            uv_901INCIDENT[Code_Category]="1",
                            ADD FILTER HERE FOR CURRENT YEAR
                            )

    I thought about creating a DATESYTD(), but I would still need to use a filter, which would require to be changed each year (I think). 

    Thanks for any help. Brent


    Brent

    Monday, November 24, 2014 10:31 PM

Answers

  • Create the following formula in myCalendar. This will give you a 'moving' filter based on today's date and you can reference the 'Y' value in your CUBEVALUE formula.

    =IF(YEAR([Date]) = YEAR(TODAY()) = TRUE, "Y", "N")

    Regards

    • Proposed as answer by Michael Amadi Tuesday, November 25, 2014 9:58 AM
    • Marked as answer by bvanscoy678 Tuesday, November 25, 2014 2:43 PM
    Tuesday, November 25, 2014 9:56 AM

All replies

  • Create the following formula in myCalendar. This will give you a 'moving' filter based on today's date and you can reference the 'Y' value in your CUBEVALUE formula.

    =IF(YEAR([Date]) = YEAR(TODAY()) = TRUE, "Y", "N")

    Regards

    • Proposed as answer by Michael Amadi Tuesday, November 25, 2014 9:58 AM
    • Marked as answer by bvanscoy678 Tuesday, November 25, 2014 2:43 PM
    Tuesday, November 25, 2014 9:56 AM
  • Perfect!

    Thank you,

    Brent


    Brent

    Tuesday, November 25, 2014 2:43 PM