Trend line of KPI's past values

[leg14]

let's say I have a KPI which is a simple calculation like

SUMX(FILTER(Table;Table[Date] < Today());Table[Column])

I would like to plot a time series of the KPI's past values (for example for the first day of every month since 01-01-2014), so for instance it is enough to create a new table - Trend_Table - with two columns - DATE, KPI and calculate each row's KPI value as

SUMX(FILTER(Table;Table[Date] < Trend_Table[Date]);Table[Column])

(it may not exactly work that way in DAX but you get the idea)

The problem is I really want to have online values, which means that once the September begins a new row with DATE = '01-09-2018' should be added automatically to the table 'Tren_Table'. Is it possible in Power BI? Any referalls greatly welcomed.

[v-danhe-msft]

Hi @leg14,

From your description, I could not figure out how could you create the new table and how the two columns in 'Trend_Table' related to the row table? Could you please post me more pictures of your data structure and your desired picture or share pbix file if possible?

Regards,

Daniel He

[Anonymous]

You can create DAX Table , instead of creating the physical table.That way, DAX table will become dynamic when you refresh the data , so based on your underlying table data, you will see records for the current month as well.

Thanks

Raj

[Anonymous]

You could calculate a new calcualted table or calculate sumx for all days using the ALLEXCET  field, for example.

KPI = CALCULATE(SUM(Fact[Sales]), ALLEXCEPT(Fact, Fact[SaleDate]))

It kinda works like a group by field.

ALLEXCEPT

Calculated Table in DAX