Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leg14
New Member

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
v-danhe-msft
Employee
Employee

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.
Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.