Live/ Dynamic Week Sales

DAX DAX Calculations
1

Live Weeks Approval.pbix (64.6 KB) Approvals.xlsx (9.7 KB)

Dear DNA Team,

I have created a rolling calendar using m-code.

The attached excel data is loaded to PBI model.

I am trying to calculate live/dynamic totals of approval per week but I would like to see only for 2-3 weeks from today date and 2-3 weeks from the last approval date from the data model.

The data model goes from 18/01/2021 to 08/02/2021 (weekly).

Going back 2 weeks from last approval date from data model I tried to use this:

Trial 1 = CALCULATE([Approvals],

FILTER(ALL(‘Date’),

SELECTEDVALUE(‘Date’[Date]) >= MAX(Data[Approval]) - 14 &&

SELECTEDVALUE(‘Date’[Date]) >= MAX(Data[Approval])))

but unfortunately, I get the wrong result.

Then I applied different method using:

Trial 2 =

VAR currentdate = SELECTEDVALUE(‘Date’[Date])

Return

IF(currentdate >= MAX(Data[Approval]) - 14 && currentdate <= MAX(Data[Approval]), [Approvals], 0)

But again the formula did not work 100% correctly as it returned the results for week 4-2021.

I thought I can apply today into the formula as I would like to calculate number of approvals per week going back 2 weeks back from today date:

Trial 3 =

IF(SELECTEDVALUE(‘Date’[Date]) >= TODAY() -14 && SELECTEDVALUE(‘Date’[Date]) <= TODAY(),
[Approvals], 0)

Trail 4 =

CALCULATE([Approvals],
FILTER(ALL(‘Date’),
SELECTEDVALUE(Data[Date]) >= TODAY() -14 && SELECTEDVALUE(Data[Date]) <= TODAY()))

Could you please advise/ help?

I will appreciate your help,

Thank you,

Mateusz

2

The result that I am looking for is:

From Today’s date and from the last date in the data.

image

3

Hi @Matty

Please Review Problem of the Week #5 - Dynamic Last N Weeks Trend

Video with the solution Link

4

Hi @jbressan

I have watched the video and this isn’t helpful as I don’t have a week offset in my calendar also the result that I am trying to return is slightly different since I want to provide the result in the context of week and year.

5

Hi @Matty

Try this for Card Visualization

Total Last 3 Week Approvals = 
VAR _CurrentWeek = WEEKNUM(LASTDATE(Data[Date]))
VAR _LastDay = CALCULATE(MAX('Date'[Date]), ALL(Data[Date]), 'Date'[Week Number] = _CurrentWeek)
VAR _FirstDay = CALCULATE(MIN('Date'[Date]), ALL(Data[Date]), 'Date'[Week Number] = _CurrentWeek-2)

VAR _Result = 
    CALCULATE([Approvals],DATESBETWEEN('Date'[Date],_FirstDay,_LastDay))

Return _Result

image_2021-02-23_101520

Note: For long time solution add the WeekOffSet to you Calendar Table, and follow the pattern for Weeks Calculation

6

Hi @jbressan

This still isn’t what I am looking for and I asked for help with it. Apologies for being direct, I do appreciate your help. I don’t understand why I should still add Offset week if this is not needed in my scenario.

Your formula kind of gave me a hint to obtain the result that I asked for.

The calendar contains a recurring calendar once you refresh it on daily basis it will add a new date.

image

The data of approvals contains only data for 4 weeks:

image

As I mentioned I wanted to always return 2-3 weeks of the data from Todays date and then also from the last max date from the data table.

Using Today date I manage to get dynamic result:

The result is provided per Year-week as I mentioned.

Trial 5 =

IF(

[Min Date] >= TODAY() -28

&& [Min Date] <= TODAY(), [Approvals],BLANK())

image

Your formula works fine for the card but I think there is no need to make it so long and I can easily use:

Trial 6 =
CALCULATE([Approvals],FILTER(ALL(‘Date’),
[Min Date] >= TODAY() -28
&& [Min Date] <= TODAY() ))

image

I also applied the same formula but for max date in the data table:

Trial 7 =

VAR MAX_DATE = CALCULATE(MAX(Data[Date]),ALL(Data))

Return

IF([Min Date] >= MAX_DATE -14 &&
[Min Date] <= [Max Date],[Approvals],BLANK())

image

Now the data would always return the results for 2-3 weeks :slight_smile:

Min Date = MIN(Data[Date])

Please find the attached file Live Weeks Approval.pbix (66.8 KB)