CODEWITHSUNDEEP
powerbidax
JoseLuiz
JoseLuiz

Reputation: 11

How to Show last period (Fiscal Year) of sale, based on multi-filters report

I tried create a dashboard based on fiscal year, with more Filters, like region, sales rep name, ...

Example files avaliable on dropbox:

https://www.dropbox.com/sh/l25kdz6enmg35yb/AABPuOk3kKOpfQdKDfRUcnX2a?dl=0

On my closest attempt, i tried this follow:

Add one column on my data set, naming each period as distinct number, like: "17";"18";"19", due to deslocated fiscal year (april to march).

Then create a measure:

PREVIOUS CROP_YEAR = SWITCH(TRUE();
SELECTEDVALUE('dataset'[Crop-X])=16;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
SELECTEDVALUE('dataset'[Crop-X])=17;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
SELECTEDVALUE('dataset'[Crop-X])=18;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
SELECTEDVALUE('dataset'[Crop-X])=19;(CALCULATE(SUM('dataset'[Order Value]);ALL('dataset')));
0)

Expected output was: Values based on all filters applied, But instead i just get an empty charts

Upvotes: 1

Views: 70

Answers (1)

Jelle Hoekstra
Jelle Hoekstra

Reputation: 672

The measure is return the total because you are explicitly asking for it by using the ALL function. This removes all the filters from the dataset thus returning a grand total. This can work but it creates a complexity in your dataset with respect of having two time dimensions. The way to solve this is to first make sure you filter the date correctly with respect to both dimensions

PREVIOUS YEAR = 
    CALCULATE(
        SUM('dataset'[Order Value]);
        FILTER( 
            ALL ( 'dataset' ) ;
            AND ( 
                'dataset'[Crop-X] = MAX('dataset'[Crop-X]) -1 ;
                'dataset'[YEAR] = MAX('dataset'[YEAR] ) -1
            )
        )
    )

Furthermore, this measure still uses the ALL function which means any other filters get ignored. Using ALLSELECTED instead would result in the relative time filtering to result in nothing as soon as you select any time based slicer in your dashboard, this prevents the filter from looking at any other part of the dataset that is not within the primary sliced dataset. The workaround would be to use ALLEXCEPT and add the filters you want to be able to use as arguments. Downside is that any filter you add to your dashboard will have to be added to the exception manually.

PREVIOUS YEAR = 
    CALCULATE(
        SUM('dataset'[Order Value]);
        FILTER( 
            ALLEXCEPT( 'dataset' ; Dim1[Group] ; Dim1[Manager] ; Dim1[Region] ) ;
            AND ( 
                'dataset'[Crop-X] = MAX('dataset'[Crop-X]) -1 ;
                'dataset'[YEAR] = MAX('dataset'[YEAR] ) -1
            )
        )
    )

Upvotes: 1

Related Questions