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
9 years ago

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'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