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

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

mbidelski's avatar
mbidelski
Helper I
2 years ago

Quick followup question, I tried putting your code in a DAX measure and this fragment specifically:

var CurrDate =     PARALLELPERIOD (
        LASTNONBLANK ( '_CALENDAR'[Date], [JP] ),
        0,
        MONTH
    )

throws up an error:

The calculation group item works like a charm btw, so not sure what's wrong here?

  • 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