Forum Discussion

mbidelski's avatar
mbidelski
Helper I
2 years ago
Solved

Timeintelligence calculation group

Hi everyone,   I have a YoY calculation item     VAR PREV = CALCULATE ( SELECTEDMEASURE (), sameperiodlastyear ( '_CALENDAR'[Date]) ) RETURN IF(OR(ISBLANK(PREV),ISBLANK(SELECTEDMEASURE ())),BLA...
Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi mbidelski 

    You could use LASTNONBLANK to identify the last date where SELECTEDMEASURE() is nonblank, then expand this to a month with PARALLELPERIOD.

     

    This should leave the left chart unchanged, but adjust the right chart to display just the latest month.

     

    Here is how I would write it.

    I slightly rewrote the final expression after RETURN, but you don't have to change that.

    -- Find the last month for which SELECTEDMEASURE() is nonblank
VAR LastMonthAvailable =
    PARALLELPERIOD (
        LASTNONBLANK ( 'Date'[Date], SELECTEDMEASURE ( ) ),
        0,
        MONTH
    )
VAR PREV =
    CALCULATE (
        SELECTEDMEASURE ( ),
        SAMEPERIODLASTYEAR ( LastMonthAvailable )
    )
VAR CURR = CALCULATE ( SELECTEDMEASURE ( ), LastMonthAvailable )
RETURN
    IF (
        AND ( NOT ISBLANK ( PREV ), NOT ISBLANK ( CURR ) ),
        DIVIDE ( CURR - PREV, PREV )
    )

    Does this work for you?

    Regards

OwenAuger's avatar
OwenAuger
Super User
2 years ago

Glad the calc group solution worked 🙂
Regarding your follow-up question - what is the complete measure that you are testing?

 

The PARALLELPERIOD expression returns a table, specifically a month-worth of dates.

As this is a table rather than scalar value, it cannot itself be returned by a measure, or used anywhere that expects a scalar value.

 

Based on the error message, it appears that the CurrDate variable is being used somewhere that expects a scalar value.

mbidelski's avatar
mbidelski
Helper I
2 years ago

Thank you for your help! Here's the entire measure, it's essentially the same as the calculation item, just for a card visual.

and here's the error:

and the error when I have it return just the Current Date:

 

 

 

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Thanks 🙂

    The first problem I can see is that your measure appears to be making use of filters from the '_Time Intelligence' calculation Group table.

    This won't work, because if the calculation group column is filtered it will be applied to this measure, which you don't want in this case. The first error relates to the calculation group trying to apply to this measure when it returns text.

    You will need to create an independent parameter table to allow switching for this measure.

     

    One other question: Do you plan to replace "" with numerical values? A the moment, I can't see where this measure returns any result that isn't text.

     

    Regards

    • mbidelski's avatar
      mbidelski
      Helper I
      2 years ago

      Briliant advice once again, indeed it was the calculation item causing the problem. But this causes another problem, how do I control the calculation group with an independent parameter? Right now the the Calc Group is in a filter:

      so controlling it with an independent param - I guess I would need to check it from within the timeintelligence measures...?

       

      One other question: Do you plan to replace "" with numerical values? A the moment, I can't see where this measure returns any result that isn't text.

      No, that's just a blank space, here is how it looks like when it works:

      So essentially it either shows no data or is invisible.