The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
My problem is as follows:
I have facts table containing Actual YTDs entered at each quarter.
This table is composed of the following fields:
Actual YTD (int) | Date (DateTime of creation) | Project Code (Text) | Quarter (Text -> Q1 2024, etc.)
I'd like to sum up the values for each quarter, but the problem is that within a quarter, a project doesn't necessarily have a new Actual YTD, and each Actual YTD entered by Quarter represents the cumulative total of the previous Quarter and the current Quarter.
However, I'd like to retrieve the most recent Actual YTD before that quarter, if it doesn't have an Actual YTD.
I'm having trouble thinking about creating a measure to solve this problem. Could you help me?
Thanks in advance
Solved! Go to Solution.
Hi @Laveezai
I found your post while I was checking the forums, has your problem been solved? If not, here is the method I provided:
Here's some dummy data
"Table"
Create measures.
MarkNum =
VAR _MaxQuarter = CALCULATE(MAX('Table'[Quarter]), ALL('Table'))
var _codeMaxQuarter = CALCULATE(MAX('Table'[Quarter]), FILTER(ALL('Table'), 'Table'[Project Code] = MAX('Table'[Project Code])))
RETURN
IF(
_codeMaxQuarter = _MaxQuarter,
SELECTEDVALUE('Table'[Project Code]),
0
)
Sum Actual YTD =
VAR _MaxQuarter = CALCULATE(MAX('Table'[Quarter]), ALL('Table'))
var _CodeMaxQuarter = CALCULATE(MAX('Table'[Quarter]), FILTER(ALL('Table'), 'Table'[Project Code] = MAX('Table'[Project Code]) && 'Table'[Quarter] <> _MaxQuarter))
RETURN
IF(
'Table'[MarkNum] <> 0,
CALCULATE(
SUM('Table'[Actual YTD]),
FILTER(
ALL('Table'),
'Table'[Quarter] <= MAX('Table'[Quarter])
&&
'Table'[Project Code] = MAX('Table'[Project Code])
)
),
CALCULATE(
SUM('Table'[Actual YTD]),
FILTER(
ALL('Table'),
'Table'[Actual YTD] = MAX('Table'[Actual YTD])
&&
'Table'[Quarter] = _CodeMaxQuarter
)
)
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Laveezai
I found your post while I was checking the forums, has your problem been solved? If not, here is the method I provided:
Here's some dummy data
"Table"
Create measures.
MarkNum =
VAR _MaxQuarter = CALCULATE(MAX('Table'[Quarter]), ALL('Table'))
var _codeMaxQuarter = CALCULATE(MAX('Table'[Quarter]), FILTER(ALL('Table'), 'Table'[Project Code] = MAX('Table'[Project Code])))
RETURN
IF(
_codeMaxQuarter = _MaxQuarter,
SELECTEDVALUE('Table'[Project Code]),
0
)
Sum Actual YTD =
VAR _MaxQuarter = CALCULATE(MAX('Table'[Quarter]), ALL('Table'))
var _CodeMaxQuarter = CALCULATE(MAX('Table'[Quarter]), FILTER(ALL('Table'), 'Table'[Project Code] = MAX('Table'[Project Code]) && 'Table'[Quarter] <> _MaxQuarter))
RETURN
IF(
'Table'[MarkNum] <> 0,
CALCULATE(
SUM('Table'[Actual YTD]),
FILTER(
ALL('Table'),
'Table'[Quarter] <= MAX('Table'[Quarter])
&&
'Table'[Project Code] = MAX('Table'[Project Code])
)
),
CALCULATE(
SUM('Table'[Actual YTD]),
FILTER(
ALL('Table'),
'Table'[Actual YTD] = MAX('Table'[Actual YTD])
&&
'Table'[Quarter] = _CodeMaxQuarter
)
)
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Laveezai
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
262 | |
120 | |
115 | |
83 | |
70 |