Visualization with calculated columns

[Anonymous]

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

[v-cherch-msft · Accepted solution]

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]

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  

[v-cherch-msft]

Hi @Anonymous

Attached my simplified sample for your reference.If it is not your case,please share your sample data.You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie

[Anonymous]

Hi,

I tried your solution again and it worked.

Thank you for your help.

Sincerely 

Luca BOROME