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?