Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Hi @Anonymous ,
was your problem solved?
@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
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.