Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Visualization with calculated columns

Hi,

I have an issue on Power BI regarding the construction of a visualization.

First of all, I load the table "Historique" (excel file) which the values of the Backlog L1/Backlog L2/On hold by L1/On hold by L2 columns are fixed (see capture below)

 

 

 

In addition, I load another table in Power BI ("Fichier à plat en cours") with the same columns as in the " History " table but they are calculated columns (see capture below). However, this table is only used to calculate the Backlog L1/Backlog L2/On hold by L1/On hold by L2 of the day (in my example on 17/01/2019).

 

 

 The objective of this manipulation is to create a visualization with the values of the "History" table with as first line the daily data for the columns calculated Backlog L1/Backlog L2/On hold by L1/On hold by L2.

 

First I created a measure who make the sum of “Backlog L1”:

                                Sum backlog L1 = sum('Fichier à plat en cours'[SP_Backlog_L1])

Then I created this measure:

                       Test backlog L1 = if(SELECTEDVALUE(Historique[Report Date])<TODAY();SELECTEDVALUE(Historique[Backlog L1]);                                                         [Sum backlog L1])

 

But it doesn't work....

Can you help me?

 

Sincerely

 

Luca BOROME

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    You may create a calendar table and then link it with Historique table.Then drag the calendar date and below measure in table visual.

    Calendar = CALENDAR(MIN(Historique[Report Date]),TODAY())
    Test backlog L1 =
IF (
    SELECTEDVALUE ( 'Calendar'[Date] ) < TODAY (),
    SELECTEDVALUE ( Historique[Backlog L1] ),
    [Sum backlog L1]
)

    Regards,

    Cherie

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous

     

    You may create a calendar table and then link it with Historique table.Then drag the calendar date and below measure in table visual.

    Calendar = CALENDAR(MIN(Historique[Report Date]),TODAY())
    Test backlog L1 =
IF (
    SELECTEDVALUE ( 'Calendar'[Date] ) < TODAY (),
    SELECTEDVALUE ( Historique[Backlog L1] ),
    [Sum backlog L1]
)

    Regards,

    Cherie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      I create a calendar table and then link it with Historique table.Then drag the calendar date and the measure below in table visual.

      But when I drag the measure : 

      Calendar = CALENDAR(MIN(Historique[Report Date]),TODAY())

      My table visual do this : 

      Sincerely 

       

      Luca BOROME