Why is one measure behaving differently to similar ones on the same table?

DAX DAX Calculations
1

I am confused by a situation I have found whereby two measures from a fact table are calculating correctly, but a third is not. They all have the same logic. If I need to create a pbix to demo it I will but let me try and explain with the scenario and some code.

I have a fact table FactSales, with a date ID, ProductID, and numerical values for Sale Amount (£), Margin (£) and Sales Units. I have a date table with DateID and Week Start Date ID and the other usual helpful stuff.

In this test case the date is selected to one date using a hierarchy slicer.

My base measures are defined:

Sales Units = SUM(FactSales[Sale Quantity])

Margin Actual £ = SUM (FactSales[Margin])

Sale Amount Gross £ = SUM(FactSales[Sale Amount Gross])

And the measures built from them are:

Gross Sales Actual LW £ =
VAR _LastFullWeek = [Last Full Week Start Date ID]
RETURN
CALCULATE([Sale Amount Gross £], FILTER(ALL(dimension_Date), dimension_Date[Week Commencing Date Id] = _LastFullWeek))

MarginActual LW £ =
VAR _LastFullWeek = [Last Full Week Start Date ID]
RETURN
CALCULATE([Margin Actual £], FILTER(ALL(dimension_Date), dimension_Date[Week Commencing Date Id] = _LastFullWeek))

Sales Units LW =
VAR _LastFullWeek = [Last Full Week Start Date ID]
RETURN
CALCULATE([Sales Units], FILTER(ALL(dimension_Date), dimension_Date[Week Commencing Date Id] = _LastFullWeek))

The measure [Last Full Week Start Date ID] is defined:

[Last Full Week Start Date ID] = MIN (dimension_Date[Week Commencing Date Id) - 7

What I am seeing is that the measures for Margin and Gross Sales correctly calculate the values for sales and margin last week. But the Sales Quantity is giving me a value that happens to be the quantity sold on the the selected date. What am I doing wrong here?

2

@Jamie That’s strange maybe others as well are not calculating correctly, Power BI will definitely help here.

3

I would agree however I have tested the source data in SQL, and analyze in Excel in PBI. You’re right tho, I think I need to get a demo set up!

4

Hi @Jamie, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.