cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nardtmo
Helper II
Helper II

comparing selected date to a fix dates

HI All,

 

Thanks in advance. I would like to do below equatio in power bi. Example dates i.e 03/08/2020 week 11 sunday,

pbi results will compare average value of 02/09/2020 week 7 /sunday , 02/16/2020 week 8 /sunday, & 02/16/2020 week 9 /sunday to 03/08/2020 /week 11/ sunday  and

 

and on 03/09/2020 / Week 11 / monday will compare average value of "monday" values on week 7,8,9.

 

so basically, all future days/date will also be compared to average of "same day " on week 7,8,& 9

 

I was able to do a weekly comparison but not sure if how to add filter on variable dates

 

%Diff_BL_Volume =
VAR WK9 = CALCULATE([Volume],
Filter(all('Date Table'),
'Date Table'[week]=9
))
VAR WK10 = CALCULATE([Volume],
Filter(all('Date Table'),
'Date Table'[week]=10
))
VAR WK11 = CALCULATE([volume],
Filter(all('Date Table'),
'Date Table'[week]=11
))
VAR BL = CALCULATE((wk9+wk10+wk11)/3)
return
Divide ([volume]-bl,bl,0)

 

 

appreciate the help!!!

 

difference.jpg

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @nardtmo ,

 

We can use the following steps to meet your requirement.

 

1. We can create a measure like this,

 

%Diff_BL_Volume =
VAR WK7 =
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Date Table', 'Date Table'[day name] ),
            'Date Table'[week] = 7
        )
    )
VAR WK8 =
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Date Table', 'Date Table'[day name] ),
            'Date Table'[week] = 8
        )
    )
VAR WK9 =
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Date Table', 'Date Table'[day name] ),
            'Date Table'[week] = 9
        )
    )
VAR BL =
    CALCULATE ( ( wk7 + WK8 + WK9 ) / 3 )
RETURN
DIVIDE ( [Volume] - BL, BL, 0 )

 

2. Then we need to add two slicer, one controls week number, another controls week day name.

The result like this,

 

comparing 1.jpgcomparing 2.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

BTW, pbix as attached.

 

Best regards,


Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-zhenbw-msft
Community Support
Community Support

Hi @nardtmo ,

 

We can use the following steps to meet your requirement.

 

1. We can create a measure like this,

 

%Diff_BL_Volume =
VAR WK7 =
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Date Table', 'Date Table'[day name] ),
            'Date Table'[week] = 7
        )
    )
VAR WK8 =
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Date Table', 'Date Table'[day name] ),
            'Date Table'[week] = 8
        )
    )
VAR WK9 =
    CALCULATE (
        [Volume],
        FILTER (
            ALLEXCEPT ( 'Date Table', 'Date Table'[day name] ),
            'Date Table'[week] = 9
        )
    )
VAR BL =
    CALCULATE ( ( wk7 + WK8 + WK9 ) / 3 )
RETURN
DIVIDE ( [Volume] - BL, BL, 0 )

 

2. Then we need to add two slicer, one controls week number, another controls week day name.

The result like this,

 

comparing 1.jpgcomparing 2.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

BTW, pbix as attached.

 

Best regards,


Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User
Super User

@nardtmo ,Not sure I got it completely

Typically same weekday is 7 days behind and same week last year is 364 days behind

 

So you can use a 7-day trailing measure

Example

sales =SUM(Sales[Sales Amount])
Day behind Sales = CALCULATE([sales],dateadd('Date'[Date],-1,Day))
diff = [sales] -[Day behind Sales]

 

To deal with Week you can refer my file

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!