Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
4 years ago
Solved

Weekly average transactions for specific Hours

Hello, I am trying to modify measure to Calculate the weekly average number of transactions between 10am and 1pm (hour is the measure) on a Saturday (day name) for each store and in total, between weeks 1 – 8 for FY23.

This is the measure that I am trying to do majority of filtering with:

 

var _min is week 1 start date and var _closingdate is week 8 end date.

 

Trial 8W Pre-period Transactions (Hours and week day) =
var _min = date(2022,01,31)
var _closingdate = date(2022,03,27)
var _saturday = 'Date'[Day Name] = "Saturday"
var _hourstart = HOUR(10)
var _hourend = HOUR(13)
return
CALCULATE (
'Actuals'[Transactions],
FILTER (
ALLSELECTED ( 'Date'),
'Date'[Date]<= _closingdate && 'Date'[Date]>= _min,
ALLSELECTED('Date'[Day Name]),
'Date'[Day Name] = _saturday,
ALLSELECTED('Time'[Time Hour]),
'Time'[Time Hour]<= _hourend && 'Time'[Time Hour]>= _hourstart
)))
 
But the measure does not want to work and as well accept 'Date'[Day Name] as a variable.
 
Above measure would be used in this measure since calculation needs to be filtered for specific stores:
 
Trial stores 8W Transactions Totals Average = CALCULATE (Averagex(Values('Store'[Store Number]) , [Trial 8W Pre-period Transactions (Hours and week day)]),FILTER (
'Store','Store'[Store Number] IN {"0137","1368","0283","0282",
"0330","0544","0981","0977","1088","1338"}))
 
If anyone know how to fix this problem I would appreciate the help.
Thanks.
Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Justas4478 

    Please refer to the code I posted in my comment above. Here it is again :

    CALCULATE (
    'Actuals'[Transactions],
    FILTER (
    ALLSELECTED ( 'Date'),
    'Date'[Date]<= _closingdate && 'Date'[Date]>= _min),
    FILTER (
    ALLSELECTED('Date'[Day Name]),
    'Date'[Day Name] = _saturday),
    FILTER(
    ALLSELECTED('Time'[Time Hour]),
    'Time'[Time Hour]<= _hourend && 'Time'[Time Hour]>= _hourstart)

    )

    Try using multiple filter clauses instead of one.

10 Replies

  • Justas4478's avatar
    Justas4478
    Post Prodigy
    4 years ago

    This is screenshot of the measure I dont know will it be of any help.

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi Justas4478 ,

    You have tried to assign two values to variable _Saturday

    It should either be 

    var _saturday = 'Date'[Day Name] 
    or 

    var _saturday = "Saturday"

     

    Also, try modifying your filter syntax

    Trial 8W Pre-period Transactions (Hours and week day) =
    var _min = date(2022,01,31)
    var _closingdate = date(2022,03,27)
    var _saturday = 'Date'[Day Name]
    var _hourstart = HOUR(10)
    var _hourend = HOUR(13)
    return
    CALCULATE (
    'Actuals'[Transactions],
    FILTER (
    ALLSELECTED ( 'Date'),
    'Date'[Date]<= _closingdate && 'Date'[Date]>= _min),
    FILTER (
    ALLSELECTED('Date'[Day Name]),
    'Date'[Day Name] = _saturday),
    FILTER(
    ALLSELECTED('Time'[Time Hour]),
    'Time'[Time Hour]<= _hourend && 'Time'[Time Hour]>= _hourstart)

    )

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! ğŸ˜Š

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      4 years ago

      rohit_singh  Day Names is a table I have in a cube. I tried to use table as variable. What option do you think would fit?

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

        Justas4478 Depends on what your requirement is. If you are looking for data specifically for Saturday, you can use 
        var _saturday = CALCULATE(VALUES('Day Name'[Day Name]), FILTER('Day Name','Day Name'[Day Name] = "Saturday" ))