Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

max date table value

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's avatar
    Anonymous
    2 years ago

    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)

     

4 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        some_bih
        Community Champion
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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)