Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Problem with shared calendar and multiple uses (different relationships)

Hi All,   This problem is bending my mind, I’m not sure if I’m just too close to it but can’t seem to figure a way around it. I’m convinced I’m missing something simple … so I’m hoping the communit...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can use the following measure to meet your requirement:

     

    Finance Impact % USERELATIONSHIP = 
DIVIDE (
    CALCULATE (
        [Project Value Act This Period],
        USERELATIONSHIP ( 'Calendar'[CalendarDate], 'Project Finances'[Reporting Period End] )
    ),
    SUMX (
        DATESINPERIOD (
            'Calendar'[CalendarDate],
            LASTDATE ( 'Calendar'[CalendarDate] ),
            -36,
            MONTH
        ),
        CALCULATE (
            SUM ( 'Project Finances'[Actual Value] ),
            FILTER (
                ALLSELECTED ( 'Project Finances' ),
                'Project Finances'[Project Status] = "Closed"
                    && 'Project Finances'[Reporting Period End]
                        IN DISTINCT ( 'Calendar'[CalendarDate] )
                    && 'Project Finances'[Project Creation Date] = [CalendarDate]
            )
        )
    ) / 36,
    0
)

     

     


    Best regards,

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can use the following measure to meet your requirement:

 

Finance Impact % USERELATIONSHIP = 
DIVIDE (
    CALCULATE (
        [Project Value Act This Period],
        USERELATIONSHIP ( 'Calendar'[CalendarDate], 'Project Finances'[Reporting Period End] )
    ),
    SUMX (
        DATESINPERIOD (
            'Calendar'[CalendarDate],
            LASTDATE ( 'Calendar'[CalendarDate] ),
            -36,
            MONTH
        ),
        CALCULATE (
            SUM ( 'Project Finances'[Actual Value] ),
            FILTER (
                ALLSELECTED ( 'Project Finances' ),
                'Project Finances'[Project Status] = "Closed"
                    && 'Project Finances'[Reporting Period End]
                        IN DISTINCT ( 'Calendar'[CalendarDate] )
                    && 'Project Finances'[Project Creation Date] = [CalendarDate]
            )
        )
    ) / 36,
    0
)

 

 


Best regards,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Wow ! thanks .... I can safely say I'd nevr have got that ! 

     

    I have one other question if I may ?

     

    I have another couple of measures in which I'd like to be visualized on the same shared calendar:

    Project Value Act This Period
    36M Avg Actual
     
    I've tried using your solution as a template, but this is beyond my simple DAX skills ... could you propose a solution for those too ?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Figured it out now , thanks a lot for the great support ! I'll mark complete 

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Glad to hear that you have resolved your problem. If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.


        Best regards,