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

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" ))

      • Justas4478's avatar
        Justas4478
        Post Prodigy
        4 years ago

        rohit_singh Yes apparently it needs to look at Saturday data. I manage to put what you linked in variable, But it still complains for other part of the measure. I dont know what is exactly wrong did I mess up comma or it is writen wrong?