Forum Discussion

mws5872's avatar
mws5872
Helper II
6 years ago
Solved

Calculate with Datesbetween filter and another filter

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 us...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

     

mws5872's avatar
mws5872
Helper II
6 years ago

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's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.