Forum Discussion

alforc1's avatar
alforc1
Frequent Visitor
9 years ago
Solved

Calculating share Growth from Zero Index by selecting certain date

Indexed Chart

 

 

Good Morning, 

 

I am relatively new to Power BI and more specifically to using DAX to create what I want within Power BI.

I have been through the forums and cannot find a solution to what I am looking for.

 

I have previously created the table & chart attached within Excel but I am now looking to recreate the same setup within Power BI.

 

I am attempting to create a table & chart that allows the user via a date filter to choose a specific week. Once they have selected the week via the filter that week then becomes the zero index starting point.  I then require each week after the selected zero index week to be displayed as a variance up or down vs the data held in the zero indexed week before it was displayed as Zero.

 

For example a Products sales in the selected week is at 20% but in the chart & table this now needs to be displayed as zero & the following week a Products sales are 25% so in comparison to week 5 they are +5% up from the zero index point. 

 

I have attached an example of this creation within Excel.

 

Can this be achieved in Power BI & can anyone offer some guidance as to how best to create this.

 

Any guidance would be greatly appreciated.

 

alforc1

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    alforc1,

     

    You may follow the steps below.

    1) add a calculated table and drag [Week] to Slicer

    Table2 =
DISTINCT ( Table1[Week] )

    2) add a measure

    Measure =
VAR w1 =
    MAX ( Table1[Week] )
VAR w2 =
    SELECTEDVALUE ( Table2[Week] )
RETURN
    IF (
        w1 >= w2,
        SUM ( Table1[Value] )
            - CALCULATE ( SUM ( Table1[Value] ), Table1[Week] = w2 ),
        BLANK ()
    )

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    alforc1,

     

    You may follow the steps below.

    1) add a calculated table and drag [Week] to Slicer

    Table2 =
DISTINCT ( Table1[Week] )

    2) add a measure

    Measure =
VAR w1 =
    MAX ( Table1[Week] )
VAR w2 =
    SELECTEDVALUE ( Table2[Week] )
RETURN
    IF (
        w1 >= w2,
        SUM ( Table1[Value] )
            - CALCULATE ( SUM ( Table1[Value] ), Table1[Week] = w2 ),
        BLANK ()
    )
    • rjg2g11's avatar
      rjg2g11
      Icon for Advocate I rankAdvocate I
      9 years ago

      v-chuncz-msft alforc1

       

      Hello

       

      I have just tried this and i am not getting much success. From that measure i get this;

      This is the code i am using (also, when i move my filter, the chart does not move with it).

       

       

       

      Test = 
VAR w1 =
    MAX(EPOS[Week End Date])
VAR w2 = 
    SELECTEDVALUE(Dates[Week End Date])
RETURN
    IF (
        w1 >= w2,
        SUM(EPOS[Value])
            - CALCULATE ( SUM(EPOS[Value]), EPOS[Week End Date] = w2),
        BLANK ()
    )

       

       

      Are you able to advise?

       

      Thanks

    • alforc1's avatar
      alforc1
      Frequent Visitor
      9 years ago

      Hi All & in particluar v-chuncz-msft

       

      With your help this question has now been resolved, so a big thank you from me to you.

       

      The final code used can be seen below.

       

      Index Reportable Owner =

      VAR w1 =

         SELECTEDVALUE(EPOS[Week End Date])

      VAR w2 =

          MIN(Dates[Week End Date])

      RETURN

          IF (

              w1 >= w2,

              [Share - Reportable Owner]

                  - CALCULATE ( [Share - Reportable Owner], EPOS[Week End Date] = w2),

              BLANK ()

          )

       

      Thanks again really appreciate your support.