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 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.

DayNumber
Saturday7
Sunday1
Monday2
Tuesday3
Wednesday4
Thursday5
Friday6

 

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.

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.

     

3 Replies

    • 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.