Custom Time Frame

[Anonymous]

I have an item level data set with 12 Accounts and 6 Time Frames. I need to create a custom time frame that subtracts one from another (L52 - BTS). I need this time frame to be dynamic and included in the time frame filter I already have, so I think I will need to create new lines of data for each item at each account with the new time frame. What is the best way to go about this? 

This is an example of what the data looks like 

Here are the time filters I already have that the new time will need to be added to 

Thanks! 

[v-easonf-msft]

Hi ， @Anonymous 

According to my test, @lc_finance solution works well.

Here is a demo I made according to his solution.

pbix attach 

It shows as below.

If it is,   please mark  @lc_finance  replies  as Answered to close this thread.

Best Regards,
Community Support Team _ Eason

[lc_finance · Accepted solution]

Hi @Anonymous ,

thank you for the interesting question. You can do all of this with Power Query.

Here are the steps:

1) Unpivot the columns $, Units and Volume (you can do it via the Power Query Unpivot button).

Here is the result

2) Pivot the Time Frame column. You should choose 'Value' for the values column. Here is the result

3) Add a custom column calculated as LS52 minus BTS. You can do that by clicking on Add Column -> Custom Column

then enter the formula: [L52W]-[BTS]

Here is the result

4) Unpivot the columns "ls52w", "bts" and "ls52 minus bts"

5) Pivot the column Attribute

That's it. Here is your final result:

Does this help you?

Let me know if you have more questions.

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[Tad17]

Hey @Anonymous 

I'm not entirely sure what you need that you don't already have.

You can use a slicer value in another slicer like in this link: https://community.powerbi.com/t5/Desktop/Dynamic-Slicer-based-on-Slicer/td-p/429694

You can also use selected slicer values in measures by using the SELECTEDVALUE function: https://docs.microsoft.com/en-us/dax/selectedvalue-function

you can also use the SUMX (like excel sumif) or CALCULATE(FILTER()) options to find the difference between two variables: 

1. SUMX: https://docs.microsoft.com/en-us/dax/sumx-function-dax

2. CALCULATE: https://docs.microsoft.com/en-us/dax/calculate-function-dax

3. FILTER: https://docs.microsoft.com/en-us/dax/filter-functions-dax

If this helps please kduo. 

If this solves your problem please accept it as a solution.