Forum Discussion
Custom Time Frame
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!
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
3 Replies
Hi ， Anonymous
According to my test, lc_finance solution works well.
Here is a demo I made according to his solution.
It shows as below.
If it is, please mark lc_finance replies as Answered to close this thread.
Best Regards,
Community Support Team _ EasonHi 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
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.
