compare data by hour

[Yusuf]

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                

[V-lianl-msft]

Hi @Yusuf ,

Could you please provide some sample data or sample pbix so that I can do more tests to help you solve this problem.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yusuf]

Hi @V-lianl-msft ,

Thank you for offering help.

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

Visit_at	Count
2021-10-09 07:00:00	1
2021-10-09 08:00:00 ( last week the same day and time as today now )	1
2021-10-09 09:00:00	1
2021-10-16 07:00:00	1
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 · Accepted solution]

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

[Yusuf]

@V-lianl-msft 

Actually I noticed now your measure counting everything between => last week and <= Now and what I was trying to find is  today's count until now vs lastweek  the same day until the same hour as today. example today until now  by 2021/17/11 17:12:00 count is 20 and what is the count by 2021/10/11 17:12:00 only one day calculation not all week.

Any idea on that? 

Thanks

[V-lianl-msft]

Sorry, I may have misunderstood your meaning before. Measure has been updated:

last_week = 
var last_week_start = TODAY()-7
var last_week_end = NOW()-7
return CALCULATE(COUNT('Table'[Visit_at]),FILTER('Table','Table'[Visit_at]>=last_week_start&&'Table'[Visit_at]<=last_week_end))

today = 
var _start = TODAY()
var _end = NOW()
return CALCULATE(COUNT('Table'[Visit_at]),FILTER('Table','Table'[Visit_at]>=_start&&'Table'[Visit_at]<=_end))

[Yusuf]

@V-lianl-msft 

Thank you so much! It works perfectly 🙂 

Very much appreciated to your time and support.