Forum Discussion
Getting previous period values based on slicer
I'm sure there is one little aspect i am missing in the measure i pulled from a post on this forum, but i'm trying to get the previous date (i have a weekly balance table where each row is a different date and account) data for each of our operating cash accounts. Each is grouped into a subsidiary, which is in a separate linked mapping table. The balances should be summed by subsidiary. My measure is below
Prev Week Value =
var _PrevWeek = [Prev Week]
return
Calculate(sum('Cash Position V2'[Value]),All('Cash Position V2'),'Cash Position V2'[Date] = _PrevWeek)
[Prev Week] is a dateadd measure subtracting 7 days from the date.
The measure is getting me the previous weeks data, but each subsidiary's row in the table has the entire summed balance instead of just the individual totals. hopefully this is an easy fix?
You have the same sample data for both weeks
9 Replies
You used
All('Cash Position V2')which will remove all filters. Maybe use REMOVEFILTERS instead or rely on your [Date] filter override to do all the work.
i'm getting the same result with remove filters unfortunately...
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Here's a link to the example data.
https://drive.google.com/file/d/1EJp9tLNA2_fVfzz4HkV9sBMYLCUAGavO/view?usp=drive_link
I'm looking to add a second column via a measure that gives the previous month's totals by Parent, based on the slicer selection. I know this is easy to do with the prevmonth/qtr/year functions, but this data set is by week. After thinking about it, i wonder if i need to create a table for the previous week's balances within the measure? that's a bit above my pay grade so i'm not sure how to get that done.
can you please check the link? access is denied.
Sorry about that, link is fixed
I remember why i overcomplicated this now.
The data i'm pulling in is the balance as of Friday of each week. Previous week is covered thanks to lbendlin , but I also need to display the balances at the end of the previous year, and the friday of the same week in the previous year. I tried to use DATEADD, and subtract a year, but the actual day value doesn't line up. subtracting 364 days works, but that wont scale.
I updated the data in the link to include data for those additional dates, as well as some further failed attempts to get there. I'm thinking i need to leverage week numbers, but the weeks aren't lining up (8/26/2022 is week 35, and 8/25/2023 is week 34) either.
