Forum Discussion
PowerBI - visualization settings issue
hello Dears,
Below table is my RAW Data. The number of Enrolled Users is increasing, while the number of Unenrolled Users is decreasing month by month.
| Location | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 |
Enrolled Users | Country A | 28 | 31 | 58 | 61 | 68 | 73 |
Enrolled Users | Country B | 4 | 10 | 20 | 20 | 25 | 27 |
Unenrolled Users | Country A | 49 | 46 | 19 | 16 | 9 | 3 |
Unenrolled Users | Country B | 23 | 17 | 7 | 7 | 2 | 0 |
Total Users | Country A | 77 | 77 | 77 | 77 | 77 | 76 |
Total Users | Country B | 27 | 27 | 27 | 27 | 27 | 27 |
In PowerBI, I have added two visualization components: a slicer and a donut chart. What I want to achieve is:
When no month is selected in Slicer, that the number of Enrolled and Unenrolled users displayed in the donut chart is the same as the last month (currently June), rather than a sum of the past few months. (because the total users only 103)
now, i have no idea to adjust it, sincerely for your suggestions and assistance😉
Hi Yaru
Based on the current data you show, you can also try this measure and use it in your donut chart visual. The logic to get the last month is through the _lastMonth variable which gets the last date in your table and convert it into yyyymm format.
UserCount Measure = var _lastMonth = FORMAT(MAX('Table'[Date]),"yyyymm") return IF(ISFILTERED('Table'[Date]),SUM('Table'[Users Counts]),CALCULATE(SUM('Table'[Users Counts]),FORMAT('Table'[Date],"yyyymm")=_lastMonth))Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
4 Replies
you would want to use either ISFILTERED or HASONEVALUE (personally I would use ISFILTERED):
https://learn.microsoft.com/en-us/dax/isfiltered-function-dax
This is a quick throw together but your code will look something like this:
Users = // Gets the Start of Last Month VAR __MinDate = EOMONTH ( TODAY (), -2 ) + 1 // Gets the End of Last Month VAR __MaxDate = EOMONTH ( TODAY (), -1 ) // if Date is selected, Sums user counts. If no date selected, Returns Last Month's users count. RETURN IF ( ISFILTERED ( [Date] ), SUM ( [Users Counts] ), CALCULATE ( SUM ( [Users Counts] ), FILTER ( 'Trends', Date >= __MinDate && Date <= __MaxDate ) ) )Hello Dear,
Your suggestion is very advanced, maybe I didn't adjust it well in usage. After trying, I found that there is no data displayed when clicking on months other than June. However, when not selecting any month, the data displayed is the same as the last month. Anyway, I still want to thank you.
Best Regards,
Sherry
Hi Yaru
Based on the current data you show, you can also try this measure and use it in your donut chart visual. The logic to get the last month is through the _lastMonth variable which gets the last date in your table and convert it into yyyymm format.
UserCount Measure = var _lastMonth = FORMAT(MAX('Table'[Date]),"yyyymm") return IF(ISFILTERED('Table'[Date]),SUM('Table'[Users Counts]),CALCULATE(SUM('Table'[Users Counts]),FORMAT('Table'[Date],"yyyymm")=_lastMonth))Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!many thanks Jing, it works for me, that's what i want❤️
