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
