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?
Solved! Go to Solution.
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.
Here's a link to the example data.
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.
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.
Please show the expected outcome based on the sample data you provided.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.