Calculate with Datesbetween filter and another filter

[mws5872]

Hi All,

I am trying to solve a difficult problem and I am not really an expert in DAX.

I am taking a week to date number and comparing to an 4 week average based on the day of the week the user selects. ( the WTD is solved, where I need help is on the static comparison data point) 

I do have a date table that includes week end date, day of week ( as a number) The user can ONLY select 1 date.

Day	Number
Saturday	7
Sunday	1
Monday	2
Tuesday	3
Wednesday	4
Thursday	5
Friday	6

I am looking at a 4 week data points of 2/9 to 3/8 this will always be static but I want it to calculate the average based on what user selects for date filter. For example when the user selects a date filter on my date table of 8/11 it will show both sum of 8/10 and 8/11 but then this is where it goes back to my problem.... I would then compare it to measure 3 average which inclues both monday and tuesday for weeks between 2/9 and 3/8 ( so  2/10-11,  2/17-18,  2/24-25, 3/2-3 the average of all of this) 

if they select 8/12 it will move to measure 4 and so on. I have modeled the expected output in excel I am basically trying to build this as 1 calculated measure somehow. 

https://drive.google.com/drive/folders/1ogtcXOhS0gBIVfGrj3GkRgLm7hce_H2k?usp=sharing

this is where my excel sample file is.

[amitchandak]

@mws5872 ,not very clear. But in can you need rolling week data refer

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[mws5872]

@amitchandak  not exactly solved by this. Here are some things I have tried. 

1. hard coding dates between. I even created a field in SQL that only has data for the dates mentioned.

I tried this 

SWITCH(VALUES('Date'[Weekday]),7,CALCULATE(Average(Volume[Total]),FILTER('Date','Date'[Weekday]<=7&& 'Date'[Weekday] >1)),
2,CALCULATE(Average(Volume[Total]),FILTER('Date','Date'[Weekday]=2)),
3,CALCULATE(Average(Volume[Total]),FILTER('Date','Date'[Weekday]<=3&& 'Date'[Weekday] >1)),
4,CALCULATE(Average(Volume[Total]),FILTER('Date','Date'[Weekday]<=4&& 'Date'[Weekday] >1)),
5,CALCULATE(Average(Volume[Total]),FILTER('Date','Date'[Weekday]<=5&& 'Date'[Weekday] >1)),
6,CALCULATE(Average(Volume[Total]),FILTER('Date','Date'[Weekday]<=6&& 'Date'[Weekday] >1)),
1,CALCULATE(Average(Volume[Total]),FILTER('Date','Date'[Weekday]<=7)))

Total = CALCULATE(sum(Volume[Total]))

weekday is just the day of week.

[V-lianl-msft · Accepted solution]

Hi @mws5872 ,

It's not sure if the results in the sample data you gave are correct. You can try to create the following measures

2 day sum = 
CALCULATE (
    SUM ( Data[Total] ),
    DATESINPERIOD (
        Data[Date],
        LASTDATE ( Data[Date] ),
        -2,
        DAY
    )
)

2 days avg = [2 day sum]/2

comparison =
VAR this_weekday =
    SELECTEDVALUE ( Data[Day of Wek Number] )
VAR last_weekday =
    CALCULATE (
        MAX ( Data[Day of Wek Number] ),
        FILTER ( ALL ( Data ), Data[Date] = SELECTEDVALUE ( Data[Date] ) - 1 )
    )
VAR avg_static =
    CALCULATE (
        AVERAGE ( Data[Total] ),
        FILTER (
            ALL ( Data ),
            Data[Date] >= DATE ( 2020, 2, 9 )
                && Data[Date] <= DATE ( 2020, 3, 8 )
                && Data[Day of Wek Number] IN { this_weekday, last_weekday }
        )
    )
RETURN
    DIVIDE ( [2 days avg], avg_static )

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.