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.
Hello All,
I have data as below
Region | Country | Record Date | No. of Users |
Asia | India | 10-Jan-22 | 100 |
Asia | India | 21-Feb-22 | 140 |
Asia | India | 18-Mar-22 | 136 |
Europe | France | 03-Jan-22 | 120 |
Europe | France | 17-Jan-22 | 170 |
Europe | France | 19-Mar-22 | 210 |
Europe | Germany | 13-Jan-22 | 180 |
Europe | Germany | 17-Feb-22 | 240 |
Europe | Germany | 29-Mar-22 | 280 |
Asia | China | 14-Jan-22 | 201 |
Asia | China | 21-Mar-22 | 280 |
Asia | China | 28-Mar-22 | 290 |
I have a date filter and a line chart with Region on x-axis and No. of users on y-axis. When I select a date from filter, I want the chart to show region with user count as on selected date. That is, if I select 01-Apr-22, it should show consider below data
Region | Country | Record Date | No. of Users |
Asia | India | 18-Mar-22 | 136 |
Europe | France | 19-Mar-22 | 210 |
Europe | Germany | 29-Mar-22 | 280 |
Asia | China | 28-Mar-22 | 290 |
and plot chart based on below data
Region | No. of Users |
Asia | 426 |
Europe | 490 |
Similarly, If I select 15-Feb-22, it should consider below data
Region | Country | Record Date | No. of Users |
Asia | India | 10-Jan-22 | 100 |
Europe | France | 17-Jan-22 | 170 |
Europe | Germany | 13-Jan-22 | 180 |
Asia | China | 14-Jan-22 | 201 |
and plot chart using below data
Region | No. of Users |
Asia | 301 |
Europe | 350 |
If I have country on the x-axis, I am getting required output by below DAX
Users As On Selected Date =
var _selectedate = SELECTEDVALUE('DateFilter'[Date])
var _previousdate = CALCULATE(MAX('UserData'[Record Date]),'UserData'[Record Date]<=_selecteddate)
return CALCULATE(SUM('UserData'[No. of Users]),'UserData'[Record Date]=_previousdate)
Can any of you guide me on how to achieve this at Region level.
Thanks,
Bobby
It works. There is no error as such. but, the ouput is not as expected. For example, when I select 1-Apr-22 as date, it is considering below records only
Region | Country | Record Date | No. of Users |
Europe | Germany | 29-Mar-22 | 280 |
Asia | China | 28-Mar-22 | 290 |
That is the date on or before 1-Apr-2022 at Region level. Whereas it should consider at each country level and the sum up at region level.
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |