Dax Filter for last year imports

[reymalave]

Hi guys,

I am building a measure to show the year before the latest as default (when no filter is selected). So far I  have done it with the latest year (2019).

Here are all the measures I built including latest year which is working properly:

Total imports Year before the latest=
var yes=

     CALCULATE(
          SUM('Import CIF 2016'[CIF US$]);
          SAMEPERIODLASTYEAR('Calendario Chileno'[Fecha]))

var no=

     CALCULATE(
          SUM('Import CIF 2016'[CIF US$]);
          FILTER('Calendario Chileno';[Año Anterior])
     )
return
     IF (
          [IsAnySlicerSelected?];
         yes;
         no
     )

IsAnySlicerSelected? =
     OR(
          OR(
               ISFILTERED('Calendario Chileno'[Mes Corto]);
               ISFILTERED('Calendario Chileno'[Fecha]));
               ISFILTERED('Calendario Chileno'[Año])
)

[Año Anterior] Year before the latest =

     MAX('Calendario Chileno'[Año])-1

Total imports latest year =
IF (
     [IsAnySlicerSelected?];
     SUM('Import CIF 2016'[CIF US$]);
     CALCULATE (
          SUM('Import CIF 2016'[CIF US$]);
          FILTER('Calendario Chileno'; 'Calendario Chileno'[Año]=MAX('Calendario Chileno'[Año])
          )
     )
)

Thnanks,

Reynaldo

[v-lid-msft]

Hi @reymalave ,

We can try use following measure to meet your requirement if you want to show total number of the year before lastet year when no filtered applied:

Total imports latest year =
VAR IsAnyFilter =
    IF (
        CALCULATE ( COUNTROWS ( 'Import CIF 2016' ); ALL ( 'Import CIF 2016' ) )
            - CALCULATE ( COUNTROWS ( 'Import CIF 2016' ) ) <> 0;
        TRUE ();
        FALSE ()
    )
VAR LatestYear =
    CALCULATE ( MAX ( 'Calendario Chileno'[Año] ); ALL ( 'Import CIF 2016' ) )
RETURN
    IF (
        IsAnyFilter;
        SUM ( 'Import CIF 2016'[CIF US$] );
        CALCULATE (
            SUM ( 'Import CIF 2016'[CIF US$] );
            FILTER ( 'Calendario Chileno'; 'Calendario Chileno'[Año] = LatestYear - 1 )
        )
    )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[reymalave]

Hi @v-lid-msft:

Thanks for you reply. The proposed measure partially worked it shows last years up until I select something on the year filter. I tried working it around but I cant pin point the error. You can download the pbix file here. 

The expexcted result is as follows. Show current and prior year data by default and show selected data when a filter is applied. For example, on the second page of the report "Importaciones" there are four visualizations, three cards and one filter. First card shows the total imports for the selected year, second card shows total income for the year before, third shows Year over Year and the cluster column chart shows a visual monthly comparisson of selected year a prior. The measure you provided works fine until i use the filter then it shows current year. 

Thanks for your time,

Kindly

Reynaldo

[v-lid-msft · Accepted solution]

Hi @reymalave ,

We can try to create a measure for last year: 

Total Importaciones Año Anterior Defeto UA = 
VAR LatestYear =
    CALCULATE ( MAX ( 'Calendario Chileno'[Año] ))
RETURN
    IF (
        ISFILTERED('Calendario Chileno'[Año]),
        SUM ( 'Import CIF 2016'[CIF US$] ),
        CALCULATE (
            SUM ( 'Import CIF 2016'[CIF US$] ),
            FILTER ( 'Calendario Chileno','Calendario Chileno'[Año] = LatestYear - 1 )
        )
    )

Please delete the shared link if it contain any confidential information or comes from real data.

Best regards,

[reymalave]

@v-lid-msft 

Thanks for the measure it works perfectly. 

Reynaldo

[amitchandak]

Not very clear, what is the issue you are facing