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
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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's avatar
Satish_Kumar
Helper V
9 years ago

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's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Satish_Kumar
      Helper V
      9 years ago

      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's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        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