Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Difference between two Measures with Cumulative Totals

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=eyJrIjoiNjg5ZTZiNGQtNjJhOS00NWI1LTk0NDYtNDgzMjMxZWJkN2UzIiwidCI6IjAyYWE5ZmMxLTE4YmMtNDc5OC1hMDIwLWUwMWM4NTRkZDQzNCIsImMiOjEwfQ%3D%3D

 

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

 

Regards,

Vishy

Need Help
Show and Tell
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    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

     

     

  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    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

     

     

     

     

     

     

     

14 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Anonymous

        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