Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have a measure that is a working running total for every week number and I also have another measure where it would display the last month's running total:
Ending Cash is the working running total for every week number. And Beginning Cash should display the last month's Ending Cash. However, I can't display it and I think my computation is wrong. Below is the expected output:
Hierarchy | 1 | 2 | 3 | 4 |
Beginning Cash | 481975 | 526139.24 | 700059.27 | 512847.74 |
Ending Cash | 526139.24 | 700059.27 | 512847.74 | 438.015.67 |
Data is sensitive and a lot of computations so I can't share it. Unfortunately, there is no week function on the DATEADD function.
My column axis:
Week # = WEEKNUM(B[Date].[Date],1)
My ending cash calculated measure (running total):
Ending Cash Weekly = (SUMX(FILTER(ALLSELECTED('B'[Week #]),'B'[Week #] <= MAX('B'[Week #])),[Net Cash Flow Weekly])) + 'Query - Parameter'[Past Year End Cash Value]
My beginning cash calculated measure (the one I have problems with):
Beginning Cash Weekly =
IF(SELECTEDVALUE('B'[Week #]) = 1,
[Past Year End Cash Value],
CALCULATE([Ending Cash Weekly], FILTER('B', 'B'[Week #] = SELECTEDVALUE('B'[Week #]) - 1)
)
)
My question is, how do I compute for the Beginning Cash which takes the last period's running total? Thanks for the help!
@crln-blue , You want running total till last month ? of same week on week ?
This is till date
Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Table),Table[Date] <=max(Table[Date])))
Cumm Sales till last month = CALCULATE(SUM(Table[value]),filter(allselected(Table),Table[Date] <=max(dateadd(Table[Date],-1,month))))
//with date table
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,month)))
@crln-blue , if you have date try like this for running total no need to week calculation, it can be part of date table
example
Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Table),Table[Date] <=max(Table[Date])))
Rest of the calculation is not clear to me
Hello @amitchandak , thanks for the suggestion! However, my cummulative total is working okay now. I need to use it on week num since my column axis is week num.
Edited the post, I noticed that it's a bit unclear. My problem is how do I get the last month's running total. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |