CODEWITHSUNDEEP
dax
Marco Fogale
Marco Fogale

Reputation: 33

DAX formula, crossfilter function nor returning expected result

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:

enter image description here

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

enter image description here

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

enter image description here

enter image description here

enter image description here

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:

enter image description here

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

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

enter image description here

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?

Upvotes: 1

Views: 363

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40224

I'm guessing (at least part of) the problem is that you are backing up from different end dates because LASTDATE(Sales[DocumentDate]) can return different values for different ArticleNo.

I'm not sure what value you actually want for that date, possibly LASTDATE('Dates Table'[Date]), but I'm pretty sure you want it consistent across different ArticleNo.

Upvotes: 1

Related Questions