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 ())),BLANK(), SELECTEDMEASURE () / PREV - 1)

 

 

 

that works as intended for the left chart:

But not for the one on the right - I would like it to show the latest month available, unless the user clicks the chart on the left and selects a date - then show the result for that date. It works as intended when the right hand side chart is clicked on:


But if nothing is selected it shows (sum of all job postings across all dates) / (sum of all job postings across all dates EXCEPT latest 12 months). 

 

I know I can do this using two separate measures but then I can't use the slicer in the top right to allow the user to switch between YoY and MoM.

 

Why am I even doing this, doesn't it just duplicate the result from the left chart in the one on the right? For now yes, but the right chart will show changes broken down by industry.

 

Any help much appreciated, thank you.

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

7 Replies

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