cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
setis Member
Member

Measure (LY variation)

Hi all, 

 

Could somebody please help me to do the Last Year variation of the following measure? :

 

NrCases Payment&NOFee = 
COUNTROWS(
    EXCEPT(
        FILTER(
            DISTINCT (Financial_Combined[Job No.]);
            CALCULATE ( SUM (Financial_Combined[Amount Paid]))>0
        );
        FILTER (
            DISTINCT (Fees[CaseID]);
            CALCULATE (SUM (Fees[FeeAmount]))>0
        )
    )
)

 

 

I have tried with: 

 

NrCases Payment&NOFee LY =
COUNTROWS (
    EXCEPT (
        FILTER (
            DISTINCT ( Financial_Combined[Job No.] );
            CALCULATE (
                SUM ( Financial_Combined[Amount Paid] );
                SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
            ) > 0
        );
        FILTER (
            DISTINCT ( Fees[CaseID] );
            CALCULATE (
                SUM ( Fees[FeeAmount] );
                SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
            ) > 0
        )
    )
)

 

 

 

but this gives me blank results.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Measure (LY variation)

Hi @setis 

 

If I remember your data model correctly, you'll probably need something like the following. Do bear in mind that for time intelligence functions to work properly, the date table should always have complete years and contain all the dates that you need. I.e. if you have a date table from 01/01/2017 on,  SAMEPERIODLASTYEAR(anything in 2017) will return blank since the date table has no dates in 2016.

In any case, why not just use the date table to select the year you want?

 

NrCases Payment&NOFee =
CALCULATE (
    COUNTROWS (
        EXCEPT (
            FILTER (
                DISTINCT ( Financial_Combined[Job No.] );
                CALCULATE ( SUM ( Financial_Combined[Amount Paid] ) ) > 0
            );
            FILTER ( DISTINCT ( Fees[CaseID] ); CALCULATE ( SUM ( Fees[FeeAmount] ) ) > 0 )
        )
    );
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

View solution in original post

Super User
Super User

Re: Measure (LY variation)

@setis 

 

You can always create a second measure based on the first one for the last year, i.e., you have the initial measure:

 

NrCases Payment&NOFee =
COUNTROWS (
    EXCEPT (
        FILTER (
            DISTINCT ( Financial_Combined[Job No.] );
            CALCULATE ( SUM ( Financial_Combined[Amount Paid] ) ) > 0
        );
        FILTER ( DISTINCT ( Fees[CaseID] ); CALCULATE ( SUM ( Fees[FeeAmount] ) ) > 0 )
    )
)

and then, based on that:

 

NrCases Payment&NOFee LY =
CALCULATE ( [NrCases Payment&NOFee]; SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

It's probably neater.

 

Cheers

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Measure (LY variation)

Hi @setis 

 

If I remember your data model correctly, you'll probably need something like the following. Do bear in mind that for time intelligence functions to work properly, the date table should always have complete years and contain all the dates that you need. I.e. if you have a date table from 01/01/2017 on,  SAMEPERIODLASTYEAR(anything in 2017) will return blank since the date table has no dates in 2016.

In any case, why not just use the date table to select the year you want?

 

NrCases Payment&NOFee =
CALCULATE (
    COUNTROWS (
        EXCEPT (
            FILTER (
                DISTINCT ( Financial_Combined[Job No.] );
                CALCULATE ( SUM ( Financial_Combined[Amount Paid] ) ) > 0
            );
            FILTER ( DISTINCT ( Fees[CaseID] ); CALCULATE ( SUM ( Fees[FeeAmount] ) ) > 0 )
        )
    );
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

View solution in original post

setis Member
Member

Re: Measure (LY variation)

Hi @AlB,

 

Thanks again!!

 

I needed the LY measure for a KPI card 🙂

Super User
Super User

Re: Measure (LY variation)

@setis 

 

You can always create a second measure based on the first one for the last year, i.e., you have the initial measure:

 

NrCases Payment&NOFee =
COUNTROWS (
    EXCEPT (
        FILTER (
            DISTINCT ( Financial_Combined[Job No.] );
            CALCULATE ( SUM ( Financial_Combined[Amount Paid] ) ) > 0
        );
        FILTER ( DISTINCT ( Fees[CaseID] ); CALCULATE ( SUM ( Fees[FeeAmount] ) ) > 0 )
    )
)

and then, based on that:

 

NrCases Payment&NOFee LY =
CALCULATE ( [NrCases Payment&NOFee]; SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

It's probably neater.

 

Cheers

View solution in original post

setis Member
Member

Re: Measure (LY variation)

OMG, of course. I had a very long day today. 🙂

Thanks!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 49 members 965 guests
Please welcome our newest community members: