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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jansco
Helper I
Helper I

Need help plotting a continuous 7 days of data vs average

Hi.  I need help plotting a continuous 7 days of data vs average.  Here is how my data is set up.  In Power BI I only have the Date and Sales fields.  I need to create a column with a 7 day total for the 7 days prior to the date in the row.  I also need to calculate the 30 day average from the day after the selected date. And I need to a column for the difference between the 7 day total and the 30 day average, and THAT is what I need to plot in a line graph.  Please tell me this is possible. I need some help on how to achieve this.

snippetJPG.JPG

 

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

Hi @Jansco ,

 

I am assuming you have table visual with Date,Sales,7DaysPrior,30DaysAvg ....

Try the following measures

 

1.  7DaysSalesPrior =
VAR _curDate =
    SELECTEDVALUE ( Sales7Days[Date] )
RETURN
    CALCULATE (
        SUM ( Sales7Days[Sales] ),
        FILTER (
            ALL ( Sales7Days ),
            DATEDIFF ( Sales7Days[Date], _curDate, DAY ) >= 1
                && DATEDIFF ( Sales7Days[Date], _curDate, DAY ) < 8
        )
    )

 

2.  30DaysSalesAVG =
VAR _curDate =
    SELECTEDVALUE ( Sales7Days[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Sales7Days[Sales] ),
        FILTER (
            ALL ( Sales7Days ),
            DATEDIFF ( Sales7Days[Date], _curDate, DAY ) >= 1
                && DATEDIFF ( Sales7Days[Date], _curDate, DAY ) < 31
        )
    )

 

3. 7DaysVs30DaysAvg =  [7DaysSalesPrior] - [30DaysSalesAVG]

 

Replace Sales7Days with your table name.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
CheenuSing
Community Champion
Community Champion

Hi @Jansco ,

 

I am assuming you have table visual with Date,Sales,7DaysPrior,30DaysAvg ....

Try the following measures

 

1.  7DaysSalesPrior =
VAR _curDate =
    SELECTEDVALUE ( Sales7Days[Date] )
RETURN
    CALCULATE (
        SUM ( Sales7Days[Sales] ),
        FILTER (
            ALL ( Sales7Days ),
            DATEDIFF ( Sales7Days[Date], _curDate, DAY ) >= 1
                && DATEDIFF ( Sales7Days[Date], _curDate, DAY ) < 8
        )
    )

 

2.  30DaysSalesAVG =
VAR _curDate =
    SELECTEDVALUE ( Sales7Days[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Sales7Days[Sales] ),
        FILTER (
            ALL ( Sales7Days ),
            DATEDIFF ( Sales7Days[Date], _curDate, DAY ) >= 1
                && DATEDIFF ( Sales7Days[Date], _curDate, DAY ) < 31
        )
    )

 

3. 7DaysVs30DaysAvg =  [7DaysSalesPrior] - [30DaysSalesAVG]

 

Replace Sales7Days with your table name.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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