DAX - Advanced Date Function

[Anonymous]

Hi All,

I have been struggling with this problem for a while so very hopeful someone can point me in the right direction!

I have attached a demo workbook to illustrate what I am trying to achieve here.

Explanation

I have a sales data set that is often not complete up to yesterday. Because of this I would like to give the user the ability to set their desired 'End Date'. For example if some stores have not reported for the 3 days up to yesterday, the user can kick the max date back 3 days and see the complete network sales data, but can also check the sales numbers for reporting stores 'Yesterday' if so desired.

I also want to display the selected max date sales data vs the same date prior week or the average for the rolling 4 weeks to the max date.

What I have tried

The approach I have taken is to try and create a measure for each of the series I want to display on the line chart.

For example 'Prior Week Sales Only' (see below).

I have not succeeded with this approach - I think because the row context for the line chart (Weekday) messes up the LASTDATE() or MAX([date]) variables.   

If I am going down the right path with this I feel like the answer is in setting the right filter contexts in the measure.

I am stuck on not being able to use a 'whole table' filter context for the max date as I want the user to be able to select the max date...

** Prior Week Sales Only = 
VAR selectedDate =  LASTDATE( demoData[Date] )

VAR pwEndDate_selectedDate =   (selectedDate  - (WEEKDAY(selectedDate,2)))

VAR pwStartDate_selectedDate =  (pwEndDate_selectedDate  + (1- WEEKDAY(pwEndDate_selectedDate,2)))

RETURN
    ROUND(
        CALCULATE( 
            SUM(demoData[Sales]), 
            FILTER(
                demoData,
                pwEndDate_selectedDate >= selectedDate  &&
                pwStartDate_selectedDate <= selectedDate
            )
        ),1
    )

Any help will be much appreciated

Andy

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could adjust your formula as below:

** Prior Week Sales Only = 

VAR selectedDate =  CALCULATE(LASTDATE( demoData[Date] ),ALLSELECTED(dateTable))

VAR pwEndDate_selectedDate =   (selectedDate  - (WEEKDAY(selectedDate,2)))

VAR pwStartDate_selectedDate =  (pwEndDate_selectedDate  + (1- WEEKDAY(pwEndDate_selectedDate,2)))

RETURN
    ROUND(
        CALCULATE( 
            SUM(demoData[Sales]), 
            FILTER(
                demoData,
                pwEndDate_selectedDate >= demoData[Date]  &&
                pwStartDate_selectedDate <=demoData[Date]
            )
        ),1
    )

and I think if you want to show week 35, you should use as this:

** Prior Week Sales Only 2 = 

VAR selectedDate =  CALCULATE(LASTDATE( demoData[Date] ),ALLSELECTED(dateTable))

VAR pwEndDate_selectedDate =   (selectedDate  - (WEEKDAY(selectedDate,2)))

VAR pwStartDate_selectedDate =  (pwEndDate_selectedDate  + (1- WEEKDAY(pwEndDate_selectedDate,2)))

RETURN
    ROUND(
        CALCULATE( 
            SUM(demoData[Sales]), 
            FILTER(
                demoData,
                selectedDate >= demoData[Date]  &&
                pwStartDate_selectedDate <=demoData[Date]
            )
        ),1
    )

Result:

You could use same logic for Last 4 weeks formula.

Best Regards,

Lin

[Anonymous]

Brilliant, thank you @v-lili6-msft!

I knew it was something simple that I was missing - I'm very appreciative of your solution.

Regards

Andy