Help understanding wrong results with DAX formula (crossfilter)

[MarcoFogale]

I'm obtaining wrong results from a DAX formula and I can't understand why.

In my database I have articles that are composed by multiple tools, which are produced from blank tools.  One blank can be used to produce multiple tools. I need to calculate blank sales by 3 time periods: last 6,  last 12 and last 24 months.

This is my Power BI model:

 The time period table I used for the time period slicer and the measure look like this : 

To obtain Blank's sales volumes, I created 3 measures:

When I use the last formula, which I thought would have returned the right amount of Blank sold by article by time period, I obtain strange results.

When I select "last 24 months" time period, everything looks fine:

When I select "Last 12 months", the total is fine, but the total by article is wrong:

Finally, if I select "Last 6 months" time period, all the results are totally wrong:

The curious fact is that I checked the result by executing a sql query on the database, and the DAX formula returns the right result (so 1466 for the selected time period), but only when used in a card, without filtering it by Article number.

I have no other filters that affect the visuals.

Could you help me understand why I'm not obtaining the right result, or suggest a better way to reach the desired results?

[m3tr01d · Accepted solution]

hello @MarcoFogale,

one thing I noticed in your is in the measure "Blank sales by time period", you are using LASTDATE( Sales[documentDate]) which will return the last nonblank date in the current filter context. 

The problem is the filter context is not the same in the two visual. On the one of the right, ArticleNo is in the visual so the Sales table is filtered for this Article and thus, Lastdate will return a different date in the measure.

Maybe this won't solve your issue but it would be a good start to investigate. You can test what I said using a test measure to only return the lastdate in the current filter context.

TestLastDate = LASTDATE( Sales[DocumentDate] )

Then drag this measure in your card visual (one on the left) and on your table (one on the right).

Good luck!

[MarcoFogale]

I don't know how to thank you @m3tr01d  !!

This was the only problem with my series of formulas and it was so simple I couldn't see it by myself! 

Indeed LASTDATE return the last date in the current filter context, but I wanted to calculate it from the last date ever, the more recent (in most of the cases that will be today, or yesterday, but in my case it's not so important).

I simply added ALL to the LASTDATE like this: 

CALCULATE([Blank sold quantity],

DATESINPERIOD('Dates Table'[Date],LASTDATE(ALL(Sales[DocumentDate])),-              (selectedTimePeriod),MONTH)

And suddenly the issue that hunted my afternoon disappeared...

Thank you again!

[MarcoFogale]

Thank you for your reply @Greg_Deckler . I agree with you, CALCULATE behaviour is extremely difficult to understand... However in this case, I can't really figure out another way to invert the filter and being able to filter the Sales tables by Blank ( the filter doesn't propagate from Blank table to Sales table because it can't pass from the table tools from the table articles, it's on the "many" side of the relationship).

Is there an alternative approach to filter the sales table by a blank?

[Greg_Deckler]

@MarcoFogale Well, you are probably running afoul of how CALCULATE works, which is yet another reason I don't tend to use it. If you have a filter context already in your visual and you specify a filter clause that includes that a column that already has a filter context on it, then CALCULATE replaces that filter context entirely. If it is not present, then the filter context is added to. Again, if a function requires 50 blog articles to explain how it works, it is best avoided.

This also is possibly a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907