MIN Date filtered table not returning rows

[Anonymous]

Hi all, I'm trying to get a measure to return a value for an earlier period - this could be weeks, months or someother grouping from our Dim Date.

I have a working measure which returns the sum of a table filtered by MAX('Dim Date'[DateKey]) from the date dim in the columns of a matrix.

Accounts Count:= CALCULATE(
SUM('Fact Account Balance'[Account])
,FILTER(
'Fact Account Balance'
,'Fact Account Balance'[BalanceDateKey] = MAX('Dim Date'[DateKey])
)
)

I can run this without issue and it returns the value for the first day in the period:

POP Accounts Count:= CALCULATE(
SUM('Fact Account Balance'[Account])
,FILTER(
'Fact Account Balance'
,'Fact Account Balance'[BalanceDateKey] = MIN('Dim Date'[DateKey])
)
)

We have in Dim Date a Column called PreviousDateKey which contains the DateKey for the previous day. When I run I get no rows returned:

POP Accounts Count:= CALCULATE(
SUM('Fact Account Balance'[Account])
,FILTER(
'Fact Account Balance'
,'Fact Account Balance'[BalanceDateKey] = MIN('Dim Date'[PreviousDateKey])
)
)

Ideally I'm looking for the row with POP Accounts Count to have the value of the preceding column's Accounts Count: NB the dates along the top are week ending dates and are what I'm grouping by in this case.

Any ideas how to make this work?

Thanks, RobJ.

[mwegener]

Hi @Anonymous ,

was your problem solved?

[amitchandak]

@Anonymous , refer my blogs on that, You might have club few formulae

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

refer how last period is clubbed in

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Thanks Amit,

I think I'm missing something, can you elaborate on how your last post regarding period on period measures can be used in my context please.

Thanks, RobJ.