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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
reymalave
Helper I
Helper I

Dax Filter for last year imports

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
 
 
1 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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

 

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft 

 

Thanks for the measure it works perfectly. 

 

Reynaldo

amitchandak
Super User
Super User

Not very clear, what is the issue you are facing

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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