Is there any way for to create a virtual table with item and calculated value based on the selected

[naoyixue1]

Hey there,

I have both history and future two fact tables. I want to combine them as a one table at item and calculated value based on the selected week to inlcude how many weeks before data and how many weeks after data.  After that,  I then can calculate the cumulative percentage at item level to get A, B and C ranking.  But I have a problem to combine those two tables as a final one based on the selected week at below format(only Item and Value). Do you have any thoughts how I can solve that one? 

For example,

If I select History Before 2, it means i will only take part of data in History table based on 2 weeks ago history. In other words, I will grab the data with one week before (Week - 1) and two weeks before (Week -2) ---highlighted in pink

If I select History after 3, it means i will only take part of data in Future table based on 3 weeks after future. In other words, I will grab the data with one week after (Week + 1), two weeks after (Week +2), and three weeks after (Week +3) ---highlighted in pink

Then I can use union dax to combine to get a final virtual table with item and value in order to calculate the cumulative percentage at item level. 

Any thoughts? Really really appreciate that

[naoyixue1]

Good morning! Hey all,

Any thoughts there? Really appreciate your help and efforts!

[naoyixue1]

@foodd  Sorry, I thought I posted the attachement, but it seems not. Let me reload the attachment (see as below) 

https://1drv.ms/u/s!AobyozIQwx-9k5xk-BWn_PD03ezLig?e=SU3OEC

Ideal Table for ABC analysis.xlsx

In below chart, I just highligt my ideal table in red to only have the item and related value based on the selected week. Given that, I can calculate the cumulative percentage at each item to identify the A,B and C ranking.  Do you have any thoughts there? Thanks 

[naoyixue1]

Any thoughts though? Thanks yall!

[foodd]

Hello @naoyixue1 , and thank you for sharing a question with the Community.  This reply is informational. Please follow the decorum of the Community Forum when asking a question.

Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!