How to declare a variable?

[Satish_Kumar]

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(FY15-16))/Last Year(FY15-16) for calculating YOY. I have to a financial year column and using it as slicer.

Now, the concern is, if I select FY15-16, I want that YOY to get updated accordingly i.e.,  FY15-16 should be current year now and FY14-15 should be last year. So YOY should show corresponding growth (Growth of FY15-16 over FY14-15).Similarly growth should be updated for other selections as well.

Similarly for QOQ, YTD & QTD, we have the same requirement. So kindly guide me as to what steps should I follow to achieve it.

Regards,

Satish Kumar

[Anonymous]

---

Similarly for QOQ, YTD & QTD, we have the same requirement. So kindly guide me as to what steps should I follow to achieve it.

---

 Specifically, what are you trying to achieve?

Can you send a picture of how your data is stored, or possibly create some sample data?

Best,

Martin

[Satish_Kumar]

@Anonymous 

This is what i want to achive.

Regards

Satish

[Anonymous]

@Satish_Kumar

I understand. Did you have any success with it? I need to see a sample of how your data is stored in order to help you.

[Satish_Kumar]

@Anonymous

I just use the a table called "General Ledger Entry".

And i am fetching the data from SQL database.

Regards,

Satish Kumar

[v-yulgu-msft]

Hi @Satish_Kumar,

Please refer to this article to provide more useful information to illustrate your scenario, so that we can test for you.

Regards,
Yuliana Gu

[Satish_Kumar]

Hi @v-yulgu-msft,

Thanks for the article i will keep this in mind before posting next time.

Actully if i talking about the scenario, I have to compare YOY and for that then i have to apply the filter of financial year.

And when we applied the filters the data get shorted according to that but the "CARD" chart is fix for only one measure

and it show only value that was calculated.

And if i want to see the comparison of two different year which i select through filter then the "CARD" should show the result depending on the filter selection.

So for that i need two variable where i can store the filter values and remainning calculation will be done according to the years stored in the variables.

Currenty i just fixed the card with a two years (can say i just made it static) and when i select random value then it show me null.

 Regards,

 Satish Kumar

[v-yulgu-msft]

Hi @Satish_Kumar,

Below is a simple example in my test.

Suppose the simple table structure looks like.

Create a calculated table which lists unique FY values.

New Table = VALUES(Table[FY])

Year Number = RIGHT(New Table[FY],2)

Last FY = LOOKUPVALUE(New Table[FY],New Table[Year Number],New Table[Year Number]-1)

Then, create measures.

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

YOY = ([Current year value]-[Last Year])/[Last Year]

Add 'New Table'[FY] into slicer.

Best regards,
Yuliana Gu

[Satish_Kumar]

@v-yulgu-msft

I tried the solution given by you but some where i failed.

Take a look and guide me where i did the mistake.

Regards,

Satish Kumar

[v-yulgu-msft]

Hi @Satish_Kumar,

From current information, it's hard to say why it doesn't return desired result. Please check the measures for [current year value] and [last year value] in my original post, especially the last year:

Last Year =
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER ( Table, Table[FY] = LASTNONBLANK ( New Table[Last FY], 1 ) )
)

 If possible, please share your .pbix file for further analysis.

Regards,
Yuliana Gu

[Satish_Kumar]

Hi @v-yulgu-msft

here the link for my PBIX file.

https://drive.google.com/file/d/0Bxq6NdcHyxydck0taTBzNGtGT28/view?usp=sharing

Regards,

Satish Kumar

[v-yulgu-msft]

Hi @Satish_Kumar,

I have made some adjustment in your .pbix file.

Remove the relationship between two tables.

Pay attention to the content in bold.

Current year value =
CALCULATE (
    SUM ( Sales[Sale] ),
    FILTER (
        Sales,
        Sales[FinancialYearDisplay]
            = LASTNONBLANK ( 'New Table'[FinancialYearDisplay], 1 )
    )
)

Last Year =
CALCULATE (
    SUM ( Sales[Sale] ),
    FILTER (
        Sales,
        Sales[FinancialYearDisplay] = LASTNONBLANK ( 'New Table'[Last FY], 1 )
    )
)    

Regards,
Yuliana Gu

[Satish_Kumar]

@v-yulgu-msft

Sorry for Replying Late......

The code which you send me is very much helpful.

Can I create a new table with more than one value used?

Like we did in New Table = VALUES(Sales[FinancialYearDisplay]).

I need this because I have to use month and quarters.

[v-yulgu-msft]

Hi @Satish_Kumar,

If it is a date column, you can use the same formula to create a new table:

New Table = VALUES(Sales[date])

Then, you could get month and quarter values from this date column.

Month=New Table[date].month

Month=New Table[date].quarter

Regards,
Yuliana Gu

[Satish_Kumar]

@v-yulgu-msft

Are you saying that we can just pass only one value parameter for creating a new table?

Regards,

Satish

[v-yulgu-msft]

Hi @Satish_Kumar,

No. For Values function, it only returns one column with unique values in a new table. If you need multiple columns, you can create calculated column into this new table. Or please refer to SELECTCOLUMNS function.

Regards,
Yuliana Gu

[Satish_Kumar]

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]

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

[Satish_Kumar]

Hi @v-yulgu-msft

The solution which was provided by you is very helpful to me.

But please refer to my screen shot where I can create only one filter of the financial year and this filter must be applied to the remaining fields say: Revenue, Gross Margin, PBT, Profit etc.

Currently showing default values only

So just because of this conditions I just want to create a table in which I can put multiple fields together to make my report.

And to control all the graph by single or more filters.

Regards,

Satish Kumar