Forum Discussion
Calculating a week before value
[Sum Of Cases] = SUM( CovidPerCountry[ConfirmedCovidCases] ) [7Day Rolling Sum] = var LastVisibleDate = MAX( 'Date'[Date] ) var _7DayPeriod = DATESINPERIOD( 'Date'[Date], LastVisibleDate, -7, DAY ) var Result = CALCULATE( [Sum Of Cases], _7DayPeriod, // If your 'Date' table is marked // as a date table in the model, // this last line is unnecessary. ALL( 'Date' ) ) RETURN ResultThis works but when you're nearing the beginning of time in your 'Date' table, for the first 6 days you'll get a sum over the existing days, of which there will not be exactly 7. You can either ignore this or you can change the logic so that the result is calculated only if _7DayPeriod contains exactly 7 days.
Super UserMargaretJames . please refer to my blog or video for WOW
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
amitchandak Thanks but I'm just taking the value of one day per week. This works fine.
Amount-Week before =
CALCULATE (
SUM( covidpercounty[ConfirmedCovidCases] ),
FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 && WEEKNUM(TODAY(),1)-1)
)But I need to get the value for the previous week where Weekday is 1. Could you have a look at my file above please?
Thanks.
To have correct DAX formulas that work in all circumstances you have to follow certain rules. Deviate from them and you'll be in for a surprise. So, first off, to create correct time calculations you have to have a proper date table in the model with all time pieces that you'll need. Also, forget about WEEKNUM. This function does not work the way most people would expect it to (https://dax.guide/weeknum).
To correctly do what you want you have to create a proper date table where each day will be assigned to a unique week and each week will be 7 days long as it should. WEEKNUM does not guarantee this. Once you have a date table (call it Dates) with a column with unique week identifiers (call it Year-Week and give it the format YYYY-WKXX, where XX goes from 01 through to 53), you can now create the correct calculations.
To see how such a setup should work (or a setup with some ISO Week calendars), please consult the articles at www.sqlbi.com. They have plenty of articles on how to correctly do week-based calculations. Please do not try to re-invent the wheel. It's tricky in DAX, to say the least, if you're not good at it. Saying from experience.
HI Anonymous. Thanks. Yes, I created a date table, you can see my file above. I'm just stuck on the -7 days part.
Basically I need the number 7 days before the measure below. I'm unsure how to do that.
CALCULATE ( SUM( covidpercounty[ConfirmedCovidCases] ), FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 ) )[Sum Of Cases] = SUM( CovidPerCountry[ConfirmedCovidCases] ) [7Day Rolling Sum] = var LastVisibleDate = MAX( 'Date'[Date] ) var _7DayPeriod = DATESINPERIOD( 'Date'[Date], LastVisibleDate, -7, DAY ) var Result = CALCULATE( [Sum Of Cases], _7DayPeriod, // If your 'Date' table is marked // as a date table in the model, // this last line is unnecessary. ALL( 'Date' ) ) RETURN ResultThis works but when you're nearing the beginning of time in your 'Date' table, for the first 6 days you'll get a sum over the existing days, of which there will not be exactly 7. You can either ignore this or you can change the logic so that the result is calculated only if _7DayPeriod contains exactly 7 days.