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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
juliebenedict
Frequent Visitor

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 @juliebenedict ,

 

 

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 , @juliebenedict 

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 @juliebenedict ,

 

 

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 @juliebenedict 

 

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.