Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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
@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
Million Thanks for sharing such a great logic in very simple way
Hello @v-yuezhe-msft ,
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
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!
@Anonymous,
WEEKNUM has two parameters,
More info can be found here
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?
Same problem I am also facing any solution for this?
Try this,
Last month = CALCULATE(SUM(Table[appointments]),FILTER(ALL(Table),YEAR(Table[Date])=YEAR(TODAY())&&MONTH(Table[Date])=MONTH(TODAY())-1))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |