Forum Discussion

setis's avatar
setis
Post Partisan
7 years ago
Solved

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.

Need Help
  • AlB's avatar
    AlB
    7 years ago

    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] )
)
  • AlB's avatar
    AlB
    7 years ago

    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

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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] )
)
    • setis's avatar
      setis
      Post Partisan
      7 years ago

      Hi AlB,

       

      Thanks again!!

       

      I needed the LY measure for a KPI card :)

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        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