cancel
Showing results 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

Helper III

## Calculate Formula for 7 days rolling and then median of that result by day

Step 1: Calculate Formula based on last 7 days data for different "Market"

Each of the value is created with sum of previous 7 days values. Sample in table below.

Forumula column in Date 11/7 should caluculate below value based on data from 11/1 to 11/7

Formula: =(SUM(C2:C8)+SUM(D2:D8)-SUM(E2:E8))/(SUM(F2:F8)+SUM(G2:G8))

 Date Market A B C D E F Formula 11/1/2024 New Jersey 12 345 33 54 66 442 11/2/2024 New Jersey 34 575 2 244 345 22 11/3/2024 New Jersey 442 23 223 64 41 66 11/4/2024 New Jersey 22 45 44 33 432 345 11/5/2024 New Jersey 66 7 356 2 345 41 11/6/2024 New Jersey 345 54 634 223 575 432 11/7/2024 New Jersey 41 244 546 66 23 345 0.17 11/8/2024 New Jersey 432 64 432 345 45 575 0.06 11/9/2024 New Jersey 345 33 345 41 7 23 -0.19 11/10/2024 New Jersey 575 2 575 432 54 45 -0.25 11/1/2024 Boston 23 223 23 345 244 7 11/2/2024 Boston 45 66 45 575 64 54 11/3/2024 Boston 7 345 7 23 345 244 11/4/2024 Boston 54 41 54 45 575 64 11/5/2024 Boston 244 432 244 66 23 33 11/6/2024 Boston 64 345 223 345 45 2 11/7/2024 Boston 33 575 44 41 7 546 0.68 11/8/2024 Boston 2 23 356 432 23 432 0.50 11/9/2024 Boston 223 45 634 44 45 345 0.42 11/10/2024 Boston 44 7 546 356 7 575 0.02 11/1/2024 NYC 356 23 674 634 54 23 11/2/2024 NYC 634 45 634 546 223 45 11/3/2024 NYC 546 7 546 432 44 7 11/4/2024 NYC 674 54 674 345 356 54 11/5/2024 NYC 345 244 345 575 634 244 11/6/2024 NYC 56 64 56 432 546 223 11/7/2024 NYC 565 33 565 345 432 44 0.03 11/8/2024 NYC 56 2 54 575 345 356 0.08 11/9/2024 NYC 56 223 244 23 575 634 0.08 11/10/2024 NYC 878 44 64 453 23 34 0.23

Step 2: once we have above result.

I need median by date for each day

 Date 11/1/2024 11/2/2024 11/3/2024 11/4/2024 11/5/2024 11/6/2024 11/7/2024 0.17 11/8/2024 0.08 11/9/2024 0.08 11/10/2024 0.02

2 ACCEPTED SOLUTIONS
Super User
Super User

try this

11 REPLIES 11
Super User

and try median

Super User

pls try

Helper III

STEP 1 is perfactly working but not step 2.

For each market, I have this "NSA_DCR_Median" value, now I need median for this values on daily level. "=MEDIAN(C2:C28)"

final result for below data should be (this example is only for one day.

 PERIOD_START_TIME NSA_DCR_Median 11/30/2023 0:00 0.62

Data

 PERIOD_START_TIME MARKET NSA_DCR_Median 11/30/2023 0:00 Albuquerque 1.08 11/30/2023 0:00 Atlanta 0.58 11/30/2023 0:00 Central PA 0.37 11/30/2023 0:00 Connecticut 0.62 11/30/2023 0:00 Hawaii 0.66 11/30/2023 0:00 Jacksonville 0.45 11/30/2023 0:00 LA North 0.91 11/30/2023 0:00 Las Vegas 1.42 11/30/2023 0:00 Long Island 0.60 11/30/2023 0:00 Los Angeles 0.97 11/30/2023 0:00 Miami 0.64 11/30/2023 0:00 New England 0.60 11/30/2023 0:00 New Jersey 0.50 11/30/2023 0:00 New York 0.61 11/30/2023 0:00 North Carolina 0.75 11/30/2023 0:00 Orlando 0.46 11/30/2023 0:00 Philadelphia 0.57 11/30/2023 0:00 Sacramento 0.65 11/30/2023 0:00 Salt Lake City 0.86 11/30/2023 0:00 San Diego 0.69 11/30/2023 0:00 San Francisco 0.64 11/30/2023 0:00 South Carolina 0.57 11/30/2023 0:00 Southern California 1.01 11/30/2023 0:00 Tampa 0.61 11/30/2023 0:00 Upstate NY 0.88 11/30/2023 0:00 Virginia 0.62 11/30/2023 0:00 Washington DC 0.58
Super User

try this

Helper III

when I tried by calculating it from previous measure, it's not giving correct result.

Super User

I'm no longer interested in solving all your problems in this scenario.

Helper III

thanks for your time and effort.

Super User

WHAT IS THIS AND WHERE IS COLUMN
G2:G8

Helper III

=(SUM(C2:C8)+SUM(D2:D8)-SUM(E2:E8))/(SUM(F2:F8)+SUM(E2:E8))

 Date Market A B C D E F Formula 11/1/2024 New Jersey 12 345 33 54 66 442 11/2/2024 New Jersey 34 575 2 244 345 22 11/3/2024 New Jersey 442 23 223 64 41 66 11/4/2024 New Jersey 22 45 44 33 432 345 11/5/2024 New Jersey 66 7 356 2 345 41 11/6/2024 New Jersey 345 54 634 223 575 432 11/7/2024 New Jersey 41 244 546 66 23 345 0.17 11/8/2024 New Jersey 432 64 432 345 45 575 0.05 11/9/2024 New Jersey 345 33 345 41 7 23 -0.12 11/10/2024 New Jersey 575 2 575 432 54 45 -0.16 11/1/2024 Boston 23 223 23 345 244 7 11/2/2024 Boston 45 66 45 575 64 54 11/3/2024 Boston 7 345 7 23 345 244 11/4/2024 Boston 54 41 54 45 575 64 11/5/2024 Boston 244 432 244 66 23 33 11/6/2024 Boston 64 345 223 345 45 2 11/7/2024 Boston 33 575 44 41 7 546 0.89 11/8/2024 Boston 2 23 356 432 23 432 0.52 11/9/2024 Boston 223 45 634 44 45 345 0.34 11/10/2024 Boston 44 7 546 356 7 575 0.01 11/1/2024 NYC 356 23 674 634 54 23 11/2/2024 NYC 634 45 634 546 223 45 11/3/2024 NYC 546 7 546 432 44 7 11/4/2024 NYC 674 54 674 345 356 54 11/5/2024 NYC 345 244 345 575 634 244 11/6/2024 NYC 56 64 56 432 546 223 11/7/2024 NYC 565 33 565 345 432 44 0.02 11/8/2024 NYC 56 2 54 575 345 356 0.07 11/9/2024 NYC 56 223 244 23 575 634 0.08 11/10/2024 NYC 878 44 64 453 23 34 0.27
Helper III

I have this type of data for 100 different Market. I need 7 days rolling by this formula for each market and then calculate Median based on values for each of market on daily basis.

Helper III

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors