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

Calculate running total v/s number of days since launch for separate items?

Hello community,

I have two tables that are connected through product ID. Table 1 consists of product details including date of product launch. Table 2 consists of sale details including date of sale and number of sales on each date.

Table 2 is continuously updated on a daily basis while Table 1 is maintained manually with ad-hoc updations.

The result I would like to prepare is a graph which has the number of days since launch on the x-axis and the running total of sales on the Y axis. reference image below:

 

Untitled.png

 

Joining the tables and getting the running total was something i was able to perform. however, how do i create a separate field for # of days since launch? as a calculated measure it is not possible to have it on the x-axis. Even in the pivot table, I cannot drag it as a category field. So how does one solve for this?

 

Google Drive link to sample data 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Aggregates-of-counts-over-time/td-p/281500

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

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Aggregates-of-counts-over-time/td-p/281500

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

So create a column in Table2 "Days since Launch". Use LOOKUPVALUE to lookup the launch date, then the column is:

 

Days since Launch Column =

  VAR __LaunchDate = LOOKUPVALUE('Table1,...)

RETURN

  ([Date] - __LaunchDate) * 1.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks for the input Greg. I tried that idea but it would work if there was only product. In this case there are multiple products to compare. How does one compare # of days since launch for multiple products? 

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.