cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leg14 Frequent Visitor
Frequent Visitor

Trend line of KPI's past values

 

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.

3 REPLIES 3
mnayar Established Member
Established Member

Re: Trend line of KPI's past values

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

rajendran Super Contributor
Super Contributor

Re: Trend line of KPI's past values

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

v-danhe-msft Super Contributor
Super Contributor

Re: Trend line of KPI's past values

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 35 members 945 guests
Please welcome our newest community members: