Calculating index based on monthly balances

[Pbi07]

I am trying to get an index calculation based on my data in multiple tables. 

I managed to summarize my data into below tables 

Ending Receivables for each month  

Month	Receivables for end of month
Apr-19	$42,548
May-19	$51,087
Jun-19	$38,992

Monthly sales 

Month	Invoices for the Month
Apr-19	$52,426
May-19	$29,765
Jun-19	$44,879

Receipts for the Month

Month	Receipts for the Month
Apr-19	$32,902
May-19	$42,876
Jun-19	$36,076

How can i get this calculation done for each month with the 3 summarized tables. Should this be with joins or any DAX route i can use?

[v-frfei-msft · Accepted solution]

Hi @Pbi07 ,

I have created a sample for your reference, please check the following steps as below.

1. Create a calculated table like that and make it related to the fact tables.

Table = DISTINCT('Ending Receivables for each month'[Month])

2. After that, to create a calculated column to get the excepted result.

Column = 
VAR csaled =
    CALCULATE (
        SUM ( 'Monthly sales'[Invoices for the Month] ),
        FILTER ( 'Monthly sales', 'Monthly sales'[Month] = 'Table'[Month] )
    )
VAR begi =
    MIN ( 'Table'[Month] )
VAR br =
    CALCULATE (
        SUM ( 'Ending Receivables for each month'[Receivables for end of month] ),
        FILTER (
            'Ending Receivables for each month',
            'Ending Receivables for each month'[Month] = begi
        )
    )
VAR endt =
    SUM ( 'Ending Receivables for each month'[Receivables for end of month] )
VAR ecr =
    CALCULATE (
        SUM ( 'Ending Receivables for each month'[Receivables for end of month] ),
        FILTER (
            'Ending Receivables for each month',
            'Ending Receivables for each month'[Month] = 'Table'[Month]
        )
    )
RETURN
    DIVIDE ( ( br + csaled - endt ), ( br + csaled - ecr ) ) * 100

For more details, please check the pbix as attached. If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[amitchandak]

Create a common date/month dimension.  If you try convert your month year column into a date it should work. Using that you can combine data across date/month, Any other common dimensions you should also create.

Then you can take a month from common dim and plot all these three in any visual, which can display these together or as per need.

[DataChant]

You can join all table using Power Query Editor and the Merge Queries transformation step:
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-queries

This will get you a single table with the three calculations. Then you can create measures to get your results.

Can you share a sample PBIX file with the relevant tables? I will send you the solution file. To explain each step here will take too long.

[Pbi07]

@DataChant 

Thanks for providing the steps. 

Sharing the pbix file. 

https://drive.google.com/open?id=1p_SFRm8l1bvJJnUusAwpxEFZVR5HUm9j