Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Look up previous period value without changing category

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

Need Help

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    Please try this measure:

    Comparison Value =
CALCULATE (
    SUM ( 'Fact'[Value] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Period] = SELECTEDVALUE ( 'Comparison Period'[Comparison Period] )
    )
)

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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).

       

       

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous,

         

        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