Forum Discussion

Nathan's avatar
Nathan
Frequent Visitor
9 years ago
Solved

Power BI Desktop Direct Query What-If Analysis

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 usin...
Need Help
  • Nathan's avatar
    Nathan
    9 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Nathan
Frequent Visitor
9 years ago

Anonymous,

 

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