cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pe2950 Frequent Visitor
Frequent Visitor

Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

Can someone recommend the best type of visualization to display the following data:

 

Trying to show trends in appointments, by comparing the count of appointment records created today, versus yesterday; this week vs last week; this month vs last month; this year vs last year. 

 

I currently have a bunch of cards where the value is a count of hte rows and the visual filter is set for the date range (ex, last week). Is there an easier way to do this or a better visualization to display this type of KPI?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

@pe2950

If you want to involve all the data(Today, Yesterday, This Week, Last Week, This Month, Last Month) in a single visual, create the following similar measures in your table, you may need to replace the sum function with count function.

Today = CALCULATE(SUM(Table[appointments]),FILTER(Table,Table[Date]=TODAY()))
Yesterday = CALCULATE(SUM(Table[appointments]),FILTER(Table,Table[Date]=TODAY()-1))
This month = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY()) && MONTH(Table[Date])=MONTH(TODAY())))
Last month = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&MONTH(Table[Date])=MONTH(TODAY())-1))
This week = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&WEEKNUM(Table[Date])=WEEKNUM(TODAY())))
Last week = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&WEEKNUM(Table[Date])=WEEKNUM(TODAY())-1))

Then you can create clustered column chart as shown in the following screenshot.
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
SivaMani Senior Member
Senior Member

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

@pe2950

For comparison,

 

Line and Clustered column chart would be great

Line chart also good for trend

 

Moderator v-yuezhe-msft
Moderator

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

@pe2950

If you want to involve all the data(Today, Yesterday, This Week, Last Week, This Month, Last Month) in a single visual, create the following similar measures in your table, you may need to replace the sum function with count function.

Today = CALCULATE(SUM(Table[appointments]),FILTER(Table,Table[Date]=TODAY()))
Yesterday = CALCULATE(SUM(Table[appointments]),FILTER(Table,Table[Date]=TODAY()-1))
This month = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY()) && MONTH(Table[Date])=MONTH(TODAY())))
Last month = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&MONTH(Table[Date])=MONTH(TODAY())-1))
This week = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&WEEKNUM(Table[Date])=WEEKNUM(TODAY())))
Last week = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&WEEKNUM(Table[Date])=WEEKNUM(TODAY())-1))

Then you can create clustered column chart as shown in the following screenshot.
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

Last month = CALCULATE(SUM(Table[appointments]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&MONTH(Table[Date])=MONTH(TODAY())-1))

 

If the Current month is January so last month should be Dec. But that not show any value in this case. 

 

Can you please help me how I can manage if show last year Dec detail? 

Anonymous
Not applicable

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

 When I use this Dax, But it starts the week start from Sunday to Saturday.

 

I need to set Dax in such a way that Week Start from Monday to Sunday. 

 

Give the result accordingly!

 

 

SivaMani Senior Member
Senior Member

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

@Anonymous,

 

WEEKNUM has two parameters,

  1. Date 
  2. Return type - use 1 when the week begins on Sunday; use 2 when the week begins on Monday

More info can be found here

arvindyadav Member
Member

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

Same problem I am also facing any solution for this?

SivaMani Senior Member
Senior Member

Re: Visualization To Compare Today, Yesterday, This Week, Last Week, This Month, Last Month?

@arvindyadav,

 

Try this,

 

Last month = CALCULATE(SUM(Table[appointments]),FILTER(ALL(Table),YEAR(Table[Date])=YEAR(TODAY())&&MONTH(Table[Date])=MONTH(TODAY())-1))