Get Previous Day (non consecutive) totals in a hierarchy

[bobbysasi]

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

[bobbysasi]

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.

[FreemanZ]

hi @bobbysasi 

your measure shall still work on region level, or?