Power BI Desktop Direct Query What-If Analysis

[Nathan]

I'm attempting to add a column that will provide me the last 2 weeks of sales. I've seen a few other posts where users were attempting to do this, but I'm unable to follow the same method as I'm using Direct Query (working with large datasets so not reasonable to change).

Here's the critera I'm attempting to work within (assuming it's possible):

• The "Current Date" is dynamic so it must be calculated based on the data set. Right now, I'm attempting to max the date on the data set, but this prevents me from using it in other measures.
• The Last 2 weeks of sales would be another column so I can see current sales and Last 2 Week sales side-by-side.

So, based on the above critera I've been unable to create anything useful. Is there a better way to approach this problem that I'm missing?

Here's an example of the dataset and what I'd like to have:

Desired DatasetDataset

Based on the dataset above, below is what I'm attempting to do. It obviously does not work, but should help clarify what I'm attempting to do.

L2W Sales = CALCULATE(
	SUM(dataset[Sales]),
	TOPN(2, dataset, dataset[Date], DESC)
)

[Nathan · Accepted solution]

I've finally stumbled across a solution that appears to be working for me nicely.

For anyone else running across a similar issue, I created two measures: one to calculate the max date within the data, and another to calculate the L2W sales.

MaxDate = CALCULATE(MAX(dataset[date]), ALLSELECTED(dataset))

Then I take the MaxWeekEndingDate and use it the L2W calculation.

L2W Sales = CALCULATE(SUM(dataset[Sales]),
   FILTER(
      ALLSELECTED(dataset[date]),
      dataset[date] >= (DATE(
         YEAR([MaxDate]), 
         MONTH([MaxDate]), 
         DAY([MaxDate])
      ) - 14)
   )
)

The above measures will produce the "Desired Dataset" in my original question.

And here's the post that gave me the final piece of information I needed to solve it. https://community.powerbi.com/t5/Desktop/dateadd-and-max-error/td-p/124932

[v-yuezhe-msft]

@Nathan,

Glad to hear the issue is solved. You can accept your reply as solution, this way,  other community members could easily find the solution when they get same issues.

Regards,

[v-yuezhe-msft]

@Nathan,

Could you please elaborate the logic about that how you get the desired dataset based on the sample dataset? If you want to get last 2 weeks sales from today, the L2W Sales should show the last row.

Regards,
Lydia

[Nathan]

@v-yuezhe-msft,

Yep, I've edited the post to provide one of the DAX queries that I attempted. Hopefully it helps provides some clarity to what I'm trying to do.

Ultimately, I'm trying to provide an unsummarized L2W Sales at the week level so that when I remove the Date column and add something like Item Description the L2W sales becomes aggregated to the Item Description level. There might be better ways to approach this problem, so I'm defintely open to any suggestions.

Thanks,

Nathan