Forum Discussion

Yusuf's avatar
Yusuf
Frequent Visitor
4 years ago
Solved

compare data by hour

Hello,

 

I have situation that I have to show trend of sales between today until now vs last week the same day until = today's now.

Example :   Date                                         

                  08/11/2021 11:17:00 ( date is today up to date and should count all raws until now)     

                  vs 

                  Date 

                  01/11/2021 11:17:00  ( date is last week the same day and it should count sales until the same hour as today) 

 

Result :

 

I need to show trend between two dates   i.e  number of consultation (raws) today until now = 300 , last week the same day and until the same hour it was 350  and  - % 1,16   difference something like this 🙂 

I did it in SQL =

select visit_at, count(*) from
consultation
where date(consultation.visit_at) between now() - interval 8 day and now() and weekday(consultation.visit_at) = weekday(now()) and time_format(visit_at, '%T') <= time_format((now() + interval 1 hour),'%T')
group by date(visit_at)
order by date(visit_at) asc

 

but I am not 100% confident with dax.. 

Would be appreciated if anyone got any idea

Thanks,

Yusuf                

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Try to create a measure like below:

    Measure = 
var _now = NOW()
var last_week = _now-7
return CALCULATE(COUNT('Table'[Visit at]),FILTER('Table','Table'[Visit at]>=last_week&&'Table'[Visit at]<=_now))

     

6 Replies

    • Yusuf's avatar
      Yusuf
      Frequent Visitor
      4 years ago

      Hi V-lianl-msft ,

      Thank you for offering help.

      Please see below sample table which you can refer to work on it.

      Visit_atCount
      2021-10-09 07:00:001

      2021-10-09 08:00:00 ( last week the same day and time as today now )

      		1
      2021-10-09 09:00:001
      2021-10-16 07:00:001
      2021-10-16 08:00:00 (today now)1
      I need number of count from last week the same day untill  = (today now)need to track performance if our visits were more than last week within the day and time from last week. 
      i.e=today now is = 2021-11-16 17:37:00 and number of visits until this time  20                  -->                                                                   what was the number of visit last week the same day until the same time as today -> 2021-11-09 17:37:00 number of visits ? 
        
      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        4 years ago

        Try to create a measure like below:

        Measure = 
var _now = NOW()
var last_week = _now-7
return CALCULATE(COUNT('Table'[Visit at]),FILTER('Table','Table'[Visit at]>=last_week&&'Table'[Visit at]<=_now))