topic Re: Calculating a week before value in DAX Commands and Tips

Calculating a week before value

MargaretJames — Wed, 28 Apr 2021 11:32:53 GMT

Hi,

I'm trying to calculate the total week value in a table. I would like to subtract the week before value from the current week. I'm having an issue with my week before value.

I created a measure like this:

Amount-Week before = CALCULATE ( SUM( covidpercounty[ConfirmedCovidCases] ), FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 && WEEKNUM(TODAY(),1)-1) )

But this is giving me the current week value. It should give me the previous week value. So for week 11, the week before value should be 0 and week 12, it should be 3.

I would then like to just subtract previous week from current week to calculate the total.

Total per week = Var WeekBefore = CALCULATE ( SUM( covidpercounty[ConfirmedCovidCases] ), FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 && WEEKNUM(TODAY(),1)-1) ) Var ThisWeek = CALCULATE ( SUM( covidpercounty[ConfirmedCovidCases] ), FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 ) ) Var Total = ThisWeek - WeekBefore return Var Total

I've incuded the file here. Thanks for any help.

Re: Calculating a week before value

amitchandak — Wed, 28 Apr 2021 11:35:03 GMT

MargaretJames . 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

Re: Calculating a week before value

MargaretJames — Wed, 28 Apr 2021 13:13:33 GMT

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.

Re: Calculating a week before value

Anonymous — Wed, 28 Apr 2021 21:43:10 GMT

Hi MargaretJames

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.

Re: Calculating a week before value

MargaretJames — Thu, 29 Apr 2021 08:43:59 GMT

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 ) )

Re: Calculating a week before value

Anonymous — Thu, 29 Apr 2021 11:13:15 GMT

[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 Result

This 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.