Dax help for CACULATE selected date -7

[shophoney]

Hi I have a calendar table with a column that shows the monday of the week. And I need to calculate the sales, receivings and on hand for each week.

So what I thought was take the selected week starting on a monday. And subtract all sales and receivings since then.

my dCalendar has a monday start of the week column. But my formula below doesnt work. It just says error.

1WK AGO RECEIVED:=CALCULATE('tblInvSkuMaster'[RECEIVED TOTAL UNITS],DATESBETWEEN(dCalendar[WEEKS-MON],dCalendar[WEEKS-MON]-7))


This formula works but calculates all values as of today to show what our position was 7 days ago.
UNITS RECEIVED 1WK AGO:=CALCULATE('tblInvSkuMaster'[RECEIVED TOTAL UNITS],DATESBETWEEN(dCalendar[DATES],TODAY()-7,TODAY()))

Thanks

 

[akice]

Couple of things. All three dates in DATESBETWEEN must be type Date. First parameter is a reference to a date/time Column, next 2 parameters must by Date values.

So I'm assuming the column WEEKS-MON are not a date values? and to since the 2nd and 3rd parameters need to be date values, you have to use something like MAX ( dCalendar[Date] ).

 

[shophoney]

Hi, thanks for the reply.

I have added a column in dCalendar that has the Monday-Week that is selected -7. So that is i have another column that is the date of 1 week ago from whatever week is selected.

I need to figure out how to create a formula that shows my OH at a point in time. Based on the week selected.

It will take todays OH, + units sold from selected date to today + units transfer out - units deleivered.

Thanks