Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ?
Solved! Go to 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.
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!
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!
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
70 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |