Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 community can help !.

 

Here’s the scenario, we receive a monthly excel file of project data, which has an overall status (open or closed) and budget cost vs. actual cost. The budget cost can change each month until it is closed. Projects can be open for long periods (years).

In each file, the data is cumulative – it includes all the historic records as well as the current month. For example, September file contains all data up to September, October file includes all data up to October (including September and prior) etc. It seems we can’t change the way this is exported from the source application, so we’re stuck with that.

These data files only started being received from September 2019

 

Identifying a specific month’s data – no problem, I’ve included the filename (the month end is encoded in the file name) in each row, as well as the period end (as a date and a text field) so I should be able to filter the rows that were in the September file, records that were in the October file etc.

 

The ask is to create a ratio (for each month) comparing the current month financial exposure (actual + budget) to a rolling 36-month average (actual + budget). However, the 36-month rolling average needs to be created using the Project Creation Date.

 

To add to the complexity, the calculated ratio should be in a visual using the Calendar table (there are other metrics and ratio’s which will be shown using the same calendar, based on other data) which will only show September 2019 data onwards.

 

At the minute, I have one relationship between the calendar table and the project finance table (calendar date -> project creation date) as I need that for the 36-month rolling average

 

First, the actual $ amount is calculated:

 

Project Value Act =

  CALCULATE(

    SUMX('Project Finances', 'Project Finances'[Actual Value]),

    'Project Finances'[Project Status] = "Closed"

  )

 

Then the 36-month rolling average:

36M Avg Actual =

    CALCULATE (

    AVERAGEX ( VALUES ( 'Calendar'[Short Month Short Year]), [Project Value Act] ),

    DATESINPERIOD ( 'Calendar'[CalendarDate], LASTDATE('Project Finances'[Reporting Period End]), -36, MONTH )

    )

 

“Short Month Short Year” is a text column in the calendar table in the form mmm yy like “Sep 19”.

And then the ratio:

Finance Impact % = DIVIDE([Project Value Act This Period], [36M Avg Actual],0)

 

Displaying the data in the table, all looks good:

project table.png

And when added to a visual (I’m using the text value of the period end because Power BI does some funky stuff with the rendering when month-end dates are used):

 

project line chart 1 correct.png

 

So far, no problems … but now I need to plot this on a visual that uses the same calendar table, but I will use the Short Month Short Year column for the X – axis (for output aesthetics). When I do that, obviously, the values change, because the relationship is different:

 

project line chart 2 wrong.png

 

This is not the desired behavior … so I thought maybe I can use USERELATIONSHIP instead. At the minute, I have one relationship between the calendar table and the project finance table (calendar date -> project creation date) as I need that for the 36-month rolling average. I created a second (inactive relationship) between the calendar and project finance table (calendar date -> Reporting Period End. My plan was to have measure which specified the relationship (USERELATIONSHIP) so I tried that:

 

Finance Impact % USERELATIONSHIP =

  CALCULATE(

    DIVIDE([Project Value Act This Period], [36M Avg Actual],0),

    USERELATIONSHIP('Calendar'[CalendarDate], 'Project Finances'[Reporting Period End])

  )

 

And I get different results again:

project line chart 3 userelationship.png

Which I guess makes sense, because the relationship is being used and I now lose the context of the 36-month rolling average based on the project creation date.

I’ve tried a few other things:

  • Used a separate calendar table
  • Filtered the table based on the file name
  • Use another measure the reference the result for a specific month

But none of these seem to work …

What I want to try do is take the result of the ratio calculation (e.g. 121.98% for September 2019) and plot that on a visual which uses the same calendar but only show the months September 2019 onwards.

 

Obviously, all the normal filtering by business unit (and other columns not shown like project manager etc.) needs to work as normal.

 

I've uploaded the sample files, document and PBIX here :

https://drive.google.com/drive/folders/1cC4F1sTxkgy4MWroC9CCBMQsqKqSaRpz?usp=sharing

 

any help at all would be appreciated !

 

 

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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
)

 

11.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-lid-msft
Community Support
Community Support

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
)

 

11.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.