topic Re: Measure for calculating yesterdays count vs last week count in DAX Commands and Tips

Measure for calculating yesterdays count vs last week count

zurimae — Wed, 19 Jun 2024 20:26:49 GMT

Hello,

I need help creating a measure that will show me the difference of count of phone calls received yesterday (example: Tuesday) compared to last Tuesday. For example, so i could show the skill "Intake" had 12 more calls this tuesday vs last tuesday. or the skill "stroke" had -7 less calls yesterday vs last tuesday. I have a measure to calculate how many calls less than the previous day, but i cant see to figure out how to do it from yesterday to the same day last week. below is the measure i use for 1 day difference.

Measure 2 = SUM(

'Phone Queue Data'[Total Calls Offered]

)

- CALCULATE(

SUM(

'Phone Queue Data'[Total Calls Offered]

DATEADD(

'Calendar Lookup'[Date],-1,

DAY

)

Re: Measure for calculating yesterdays count vs last week count

Jihwan_Kim — Thu, 20 Jun 2024 03:06:24 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Yesterday result: = VAR _yesterday = TODAY () - 1 RETURN CALCULATE ( SUM ( phone_queue_data[total_calls_offered] ), 'calendar'[Date] = _yesterday )

Yesterday of last week result: = VAR _yesterdayoflastweek = TODAY () - 8 RETURN CALCULATE ( SUM ( phone_queue_data[total_calls_offered] ), 'calendar'[Date] = _yesterdayoflastweek )

Re: Measure for calculating yesterdays count vs last week count

zurimae — Mon, 24 Jun 2024 14:00:23 GMT

Hello, thank you for the response. This measure just isn't calculating correctly, the difference from the two days.

If i use the measure you provided, the correct answer for "casepod" would be -4. but it's showing 74.

Here is the measure you provided (with some edits)

Yesterday of last week result: =

VAR _yesterdayoflastweek =

TODAY () - 8

RETURN

CALCULATE (

SUM ('Phone Queue Data'[Total Calls Offered] ),

'Phone Queue Data'[Date]= _yesterdayoflastweek

)