Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mbidelski
Helper I
Helper I

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:

mbidelski_0-1695742418568.png

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:

mbidelski_0-1695743138372.png


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.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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:

mbidelski_0-1695830312359.png

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

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

mbidelski_1-1695889903273.png

and here's the error:

mbidelski_2-1695889929261.png

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

mbidelski_3-1695889978102.png

 

 

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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:

mbidelski_3-1695910994584.png

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:

mbidelski_1-1695910842791.pngmbidelski_2-1695910853921.png

So essentially it either shows no data or is invisible.

 

 

 

That's briliant, thanks a million!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.