Difference between two Measures with Cumulative Totals

[vishy86]

Hi,

I am currently working on calculating the difference in cumulative totals between 2 user selected dates.

I have created some sample data (using Enter Data in Power BI) for testing purposes.

The Difference measure created is not showing the difference between the cumulative values.

The published link is as below:

https://app.powerbi.com/view?r=eyJrIjoiNjg5ZTZiNGQtNjJhOS00NWI1LTk0NDYtNDgzMjMxZWJkN2UzIiwidCI6IjAyY...

Please help. Also, any suggestions or optimal ways on calculating the cumulative totals would be appreciated. Thanks.

Regards,

Vishy

[v-lili6-msft · Accepted solution]

hi, @vishy86

As I said above, you use Edit interactions Function to keep the two columns in the same table to filter different measure.

You couldn't do that.

And here are two ways for you as a reference:

way1:

Duplicate the basic table, do Cumulative Sum 1 Written Premium and Cumulative Sum 2 Written Premium in different table.

Step1:

Add a new table

Sheet2 = Sheet1 

Step2:

Add a Year fact table

Year = VALUES(Sheet1[PROGRAM_YR] )

Step3:

Create the relationship between them like this:

Step4:

Create the total measure in different table

Cumulative Sum 1 Written Premium = CALCULATE(
SUM(Sheet1[WRITTEN_PREMIUM_AT]),
FILTER(ALLSELECTED(Sheet1[CORP_PERIOD_DT 1]),Sheet1[CORP_PERIOD_DT 1]<=MAX(Sheet1[CORP_PERIOD_DT 1])))

Cumulative Sum 3 Written Premium = CALCULATE(
                    SUM(Sheet2[WRITTEN_PREMIUM_AT]),
                    FILTER(ALLSELECTED(Sheet2[CORP_PERIOD_DT 1]),Sheet2[CORP_PERIOD_DT 1]<=MAX(Sheet2[CORP_PERIOD_DT 1])))

Difference Written Premium measure = [Cumulative Sum 1 Written Premium]-[Cumulative Sum 3 Written Premium]

By the way, Difference Written Premium should be a measure instead of column.

Step5:

Drag the CORP_PERIOD_DT 1 from different table(Sheet1 and Sheet2 ) to filter differnt measure. Do not use Edit interactions Function.

here is way1 pbix file, please try it.

Best Regards,

Lin

[Greg_Deckler]

I can't see the calculations behind your measures. Can you post the PBIX or a link to it? 

In general, kind of best practice is to use the Running Total quick measure built into Power BI. I also have another method of doing it here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[vishy86]

Hi Greg,

How do I attach the pbix file? I do not see the Attachments option when I compose a message. 

I am pasting the measures created here for your reference.

Cumulative Sum 1 = CALCULATE(

SUM(Table1[Sales]),

FILTER(ALLSELECTED(Table1[Date Filter 1]), Table1[Date Filter 1] <= MAX(Table1[Date Filter 1])))

Cumulative Sum 2 = CALCULATE(

SUM(Table1[Sales]),

FILTER(ALLSELECTED(Table1[Date Filter 2]), Table1[Date Filter 2] <= MAX(Table1[Date Filter 2])))

Difference = [Cumulative Sum 2] - [Cumulative Sum 1]

Do let me know if there is any way I can attach the pbix file and I can get that attached as well.

Thanks,

Vishy

[v-lili6-msft]

hi, @vishy86

Based on my test on your measures,

Cumulative Sum 1 = CALCULATE(

SUM(Table1[Sales]),

FILTER(ALLSELECTED(Table1[Date Filter 1]), Table1[Date Filter 1] <= MAX(Table1[Date Filter 1])))

Cumulative Sum 2 = CALCULATE(

SUM(Table1[Sales]),

FILTER(ALLSELECTED(Table1[Date Filter 2]), Table1[Date Filter 2] <= MAX(Table1[Date Filter 2])))

I guess that you should use Edit interactions Function to keep the two columns in the same table to filter different measure?

If so, you could do it like this. Here is a similar post for you refer to:

https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

If not your case, please share your sample pbix file, You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[vishy86]

Hi Lin,

The pbix file is uploaded below - 

https://bit.ly/2RGb2hO

The desired result is uploaded below - 

https://bit.ly/2DjylEZ

Figures indicate difference in cumulative values for the paramaters spread across the years for the user selected dates.

Regards,

Vishy

[Ashish_Mathur]

Hi,

Both links require signing-in

[vishy86]

Hi Ashish,

I uploaded them on OneDrive, is there any settings that I need to do before sharing so that it's accessible?

I do not see the Attachment option here when I compose a message.

Not sure, how I can share the files and results.

Please advise.

Regards,

Vishy

[Ashish_Mathur]

Try Google Drive.

[vishy86]

Hi Ashish,

The files are copied in the Google Drive below - 

https://drive.google.com/file/d/1G6Yb2jvukrY-UZWKHKVz8zTbnAryeaoW/view?usp=sharing
https://drive.google.com/file/d/1LQau_87pdTcY2K1Jx1JJO-IAsWvAaZIK/view?usp=sharing

Let me know if you face issues accessing the files.

Thanks,

Vishy

[v-lili6-msft · Accepted solution]

hi, @vishy86

Way2:

Here is a similar post for you refer to:

https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

And for your case, you could do it like this:

Step1:

Add a date table and create the relationship with Sheet1 by CORP_PERIOD_DT 1

Step2:

Add a previous date table and create the relationship with date table, make this relationship unactive

Previous Date = ALLNOBLANKROW ( 'Date' )

Step3:

Create the measure like below:

Cumulative Sum 3 Written Premium = CALCULATE (
     [Cumulative Sum 1 Written Premium], 
     ALL ( 'Date' ), 
     USERELATIONSHIP ( 'Date'[Date], 'Previous Date'[Date] )
)

Difference Written Premium measure = [Cumulative Sum 1 Written Premium]-[Cumulative Sum 3 Written Premium]

Step4:

Drag the year month column from date table and Previous Date table as slicer to filter the measure.

and here is way2 pbix file, please try it.

Best Regards,

Lin

[vishy86]

Hi Lin,

Thanks a lot for your detailed explanation and steps.

Both the methods worked but I had some questions.

The files are uploaded in the below path.

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

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

FILES:

1. Difference Report - Sample Data Method 1

2. Difference Report - Sample Data Method 2

QUERIES: 

Method 2 File: Copying Sheet1 into Sheet2 and relating it to Year table.

Q: The grand total value matches but why are the PROGRAM_YR wise values negative?

Also, another common question is why are the PROGRAM_YR wise values in Method 1 and Method 2 not matching?

Please let me know.

Thanks,

Vishy

[v-lili6-msft]

hi, @vishy86

The link for method 2 is invalid.

And I check my files they work well. for your the PROGRAM_YR wise values negative. You could filter the detail data then calculate it manually. then check if it is right.

Best Regards,

Lin

[vishy86]

Hi @v-lili6-msft ,

Long back, you had helped me on tackling this. I used your Way 2 of performing this wherein I am not duplicating the base table and creating 2 tables Date and Previous Date. The difference value results are yet to be tested by the end user.

However, there was an observation made regarding the Year Month selection. Right now for design purposes, only 1 Corp Period Date is loaded into pbix file. So, the user should only see that Corp Period Date i.e. Year Month in the dropdown. Example right now data loaded is for Aug 2019 so user should only see 201908 in dropdown. However, since I am using Year Month from Date and Previous Date tables, user is seeing all the many other Year Months which is not correct.

I created the relationships exactly how you showed me in your explanation.

Please help me with rectifying this. The Way 1 method showed by you takes care of this as you only see those corporate period dates i.e Year Months which are part of the data loaded.

Regards,

Vishy

[vishy86]

Hi @v-lili6-msft ,

For your quick reference, the pbix files are uploaded in google drive, the link for which is below - 

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

Method 1 is what I used in my report. 

The Year Month dropdown should only see the corporate period dates which are loaded in the database. In this case, it should only be Dec 2018 and Dec 2017, like is the case with Method 2 pbix file.

Please help me with this as soon as possible. Thanks a lot for all your help.

Regards,

Vishy

[v-lili6-msft · Accepted solution]

hi, @vishy86

As I said above, you use Edit interactions Function to keep the two columns in the same table to filter different measure.

You couldn't do that.

And here are two ways for you as a reference:

way1:

Duplicate the basic table, do Cumulative Sum 1 Written Premium and Cumulative Sum 2 Written Premium in different table.

Step1:

Add a new table

Sheet2 = Sheet1 

Step2:

Add a Year fact table

Year = VALUES(Sheet1[PROGRAM_YR] )

Step3:

Create the relationship between them like this:

Step4:

Create the total measure in different table

Cumulative Sum 1 Written Premium = CALCULATE(
SUM(Sheet1[WRITTEN_PREMIUM_AT]),
FILTER(ALLSELECTED(Sheet1[CORP_PERIOD_DT 1]),Sheet1[CORP_PERIOD_DT 1]<=MAX(Sheet1[CORP_PERIOD_DT 1])))

Cumulative Sum 3 Written Premium = CALCULATE(
                    SUM(Sheet2[WRITTEN_PREMIUM_AT]),
                    FILTER(ALLSELECTED(Sheet2[CORP_PERIOD_DT 1]),Sheet2[CORP_PERIOD_DT 1]<=MAX(Sheet2[CORP_PERIOD_DT 1])))

Difference Written Premium measure = [Cumulative Sum 1 Written Premium]-[Cumulative Sum 3 Written Premium]

By the way, Difference Written Premium should be a measure instead of column.

Step5:

Drag the CORP_PERIOD_DT 1 from different table(Sheet1 and Sheet2 ) to filter differnt measure. Do not use Edit interactions Function.

here is way1 pbix file, please try it.

Best Regards,

Lin