Forum Discussion

pe2950's avatar
pe2950
Helper I
8 years ago
Solved

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 yest...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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.


    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.


Regards,
Lydia

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello Anonymous ,

 

Thank you for the above information!

The last week formula is not working if the current week is January 2021 first week and the last week is December 2020 last week.

The last month formula is not working if the current month is January 2021 and the last month is  December 2020.

 

The above formulas are not showing any values in these cases. 

 

Can you please let me know how to make last week, last month formulas work in the above scenarios.

 

Thank you!

 

Regards,

Aswini