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

     

Nathan's avatar
Nathan
Frequent Visitor
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,

    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,