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.
I have a quite large model that serves a couple of purposes. One of these is to show project balances at different times and organizing these projects under their project status at that time - e.g. pipeline -> Backlog -> Production etc.
One of the measures needed here is to be able to get a value from a previous point in time for that project. However - I also still want to visualize this value for the main period view. See attached examples and PBIX file.
The result I want is to see both project A and B for under their "Period" status for 1802 which is "Backlog" but when the measure and calculate runs the returned status is from the period in question.
The very simple measure being used currently is :
Comparison Value =
CALCULATE( SUM('Fact'[Value]);
'Calendar'[Period] = VALUES('Comparison Period'[Comparison Period]) )
This is a minimal example - and there are many other dimensions and tables interconnected - so I would preffer to try to solve this in the measure using some calculate magic. Is this possible? If not can I expand the model slightly or make some other non-complicating changes that would help me out here?
Examples: Here I want the "Comparison Value" Results to be on the same row as "Sum Value" (under Backlog).
Example 2: Here is the very simplified model relationships. I would preffer not to change these too much - allthough the Project status table is solely used for this purpose so that can be restructured and changed.
The Project status table layout:
See PBIX here: PBIX Minimal Example file
Hi @troyel,
Please try this measure:
Comparison Value = CALCULATE ( SUM ( 'Fact'[Value] ), FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Period] = SELECTEDVALUE ( 'Comparison Period'[Comparison Period] ) ) )
Best regards,
Yuliana Gu
Hia,
Thanks for the answer @v-yulgu-msft. However it doesn't quite solve the issue. This is what I get when I replace the mesure in the minimal example PBIX file. What I want is for the measure for comparison value to show the 1712 figures next to the 1802 figures (even though these are on a different project status).
Hi @troyel,
What I want is for the measure for comparison value to show the 1712 figures next to the 1802 figures (even though these are on a different project status).
Based on my test, I'm afraid such an output is not achievable in that scenario.
Regards,
Yuliana Gu
Thanks for trying. I am quite sure it is possible - just have to find the right way to do it, or right way to adjust the model.
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 |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |