Forum Discussion

Aravind_Kumar's avatar
Aravind_Kumar
Frequent Visitor
3 years ago
Solved

Sum based on filter

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 sh...
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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's avatar
    v-jingzhang
    3 years ago

    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!

v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

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!