Forum Discussion

Satish_Kumar's avatar
Satish_Kumar
Helper V
9 years ago

How to declare a variable?

Hi All,   I am facing some challenges in making dynamic filters in Power BI. I am making YOY% , QOQ% , YTD , QTD for our analytics.   For Example, we are using (Current Year(FY16-17)-Last Year(FY...
Need Help
Satish_Kumar's avatar
Satish_Kumar
Helper V
8 years ago

Hi v-yulgu-msft

 

Thanks for your kind suggestion!!

 

Can we put multiple filters on the current and last year value?

Say if I applied a filter of a financial year(FY15-16) and I am willing to watch the comparison of  Jan Month of current year i.e. (FY15-16) and (FY14-15) in the same card.

And if this is possible then this will help me to a very large extent because I am trying to apply month and quarter filter on the same card and I just want to see the card value change according to the filter applied like the drill down.

 

 

Regards,

Satish Kumar

 

 

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Satish_Kumar,

 

Yes, we can put multiple filters on the measure. The logic is the same. Suppose you already have Month column and Quarter column in both source table and calculate table.

 

For example,

 

Current year value =
CALCULATE (
 SUM ( Table[Sales] ),
 FILTER ( Table, Table[FY] = LASTNONBLANK ( New Table[FY], 1 ) && Table[Quarter]=LASTNONBLANK(New Table[Quarter,1]) )
)

 

You said "see the card value change according to the filter applied like the drill down", do you mean if you only apply filters to Year slicer, while don't select any value in Month and Quarter slicer, the card shoud display the total value for whole year? If you apply filters to Year slicer and Quarter slicer, the card visual should display total value for this specific Quarter?

 

If so, maybe you could try this:

Current year vCurrent year value =
IF (
    ISFILTERED ( 'New Table'[Quarter] ) = TRUE ()
        && ISFILTERED ( 'New Table'[Month] ) = FALSE (),
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            Table,
            'Table'[FY] = LASTNONBLANK ( 'New Table'[FY], 1 )
                && 'Table'[Quarter] = LASTNONBLANK ( 'New Table'[Quarter], 1 )
        )
    ),
    IF (
        (
            ISFILTERED ( 'New Table'[Quarter] ) = TRUE ()
                && ISFILTERED ( 'New Table'[Month] ) = TRUE ()
        ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                Table,
                'Table'[FY] = LASTNONBLANK ( 'New Table'[FY], 1 )
                    && 'Table'[Quarter] = LASTNONBLANK ( 'New Table'[Quarter], 1 )
                    && 'Table'[Month] = LASTNONBLANK ( 'New Table'[Month], 1 )
            )
        ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER ( Table, 'Table'[FY] = LASTNONBLANK ( 'New Table'[FY], 1 ) )
        )
    )
)

Regards,
Yuliana Gu