Sum based on filter

[Aravind_Kumar]

Hi All : In the below matrix if i want to subtract say Column W7 - W6 or W7 - W5 how to do that.

You can all see i have filtered W4 to W7 and this will keep increasing week by week and want to show the past 4 weeks based on selection and show the variance for last week vs prior to last week ?

[Ashish_Mathur · Accepted solution]

Hi,

If you have a Date column in the Fact table then it can be easily.  If you have a Date column, then share the download link of the PBI file with a Calendar table and a week column in the Calendar table.

[v-jingzhang · Accepted solution]

Hi @Aravind_Kumar 

Can you share some sample data? Do you have daily data or weekly data? And how do you want to display the variance result?

Anyway, if you want to display the result in a separate visual, e.g. a Card, you can try a measure like below. This assumes that you have a whole number type Week column in Date table. 

variance =
VAR _lastweek = MAX ( 'Date'[Week] )
RETURN
    CALCULATE ( SUM ( 'Table'[value] ), ALL ( 'Date' ), 'Date'[Week] = _lastweek )
        - CALCULATE ( SUM ( 'Table'[value] ), ALL ( 'Date' ), 'Date'[Week] = _lastweek - 1 )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[Ashish_Mathur · Accepted solution]

Hi,

If you have a Date column in the Fact table then it can be easily.  If you have a Date column, then share the download link of the PBI file with a Calendar table and a week column in the Calendar table.