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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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 

example data.PNG

 

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

Time frame.PNG

 

Thanks! 

1 ACCEPTED SOLUTION
lc_finance
Solution Sage
Solution Sage

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

1 Unpivot.PNG

 

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

2 Pivot.PNG

 

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

3 Custom Column.PNG

 

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

 

5) Pivot the column Attribute

That's it. Here is your final result:

 

5 Pivot.PNG

 

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

 

 

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

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.

70.png

 

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

 

Best Regards,
Community Support Team _ Eason

lc_finance
Solution Sage
Solution Sage

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

1 Unpivot.PNG

 

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

2 Pivot.PNG

 

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

3 Custom Column.PNG

 

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

 

5) Pivot the column Attribute

That's it. Here is your final result:

 

5 Pivot.PNG

 

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
Solution Sage
Solution Sage

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.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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