Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Please help. Also, any suggestions or optimal ways on calculating the cumulative totals would be appreciated. Thanks.
Regards,
Vishy
Solved! Go to 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
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
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...
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.
hi, @vishy86
Based on my test on your measures,
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
Hi Lin,
The pbix file is uploaded below -
The desired result is uploaded below -
Figures indicate difference in cumulative values for the paramaters spread across the years for the user selected dates.
Regards,
Vishy
Hi,
Both links require signing-in
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
Try Google Drive.
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
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
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |