max date table value

[Anonymous]

Hi,

I feel there is something basic that I'm overlooking.

I have a date table and another table that lists sales transactions for each week (Monday to Sunday).

In the sales transaction column, I have created two columns called Start of Week and End of Week which have now been replicated in the date table. I have been counting the number of transactions within the week using datesbetween and start of week and end of week. 

How do I replicate that in the dates table? If I use Max (end of week) I get the end of the date table which is 31/12/24.

Using the formula in the table where the transactions are has been working up to a point, when I tried using it in a graph visual, there was a lot of other products that were showing as being bought but the transaction worked in a card. It was recommended by a colleague that I build these formulas within the date table which is what I'm trying to do. 

Thanks in advance.

[Anonymous · Accepted solution]

I managed to find the answer here: DAX Tables – Calendar and Time – Ville Gullstrands blogg (villezekeviking.com)

Using the following measures for last week, the week before & previous 8 weeks:

Sessions_LW = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-1) 

Sessions_2W = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)

Sessions_8W =

var _wk1 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-9)

var _wk2 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)

var _wk3 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-3)

var _wk4 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-4)

var _wk5 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-5)

var _wk6 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-6)

var _wk7 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-7)

var _wk8 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-8)

return  (_wk1 + _wk2 + _wk3 +_wk4 +_wk5 + _wk6 + _wk7 + _wk8)

[some_bih]

Hi @Anonymous it could be that you did not create Calendar / Date table in Power BI as feature?

If yes, please check link how to create it with week number. After that make connection Calendar / Date table with your fact table.

Use COUNTROWS to count transaction and filter / slicer to see it by week.

[Anonymous]

Hi @some_bih 

Ah yes, I the dates table is marked as such and there is a many to one relationship between the 'dim_date[Date] & 'landingpagemarketing[Date] which is the start of any given week. 

[some_bih]

Hi @Anonymous if max date is needed then simple MAX( yourtable_date_column) is possible option