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

    That's briliant, thanks a million!

  • 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: