cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
geirf Frequent Visitor
Frequent Visitor

Problem with a dax query

Hi!

 

Hope somebody can help me out with a problem with a semi additive measure. In my dataset there is a hole in the history as you see in the first picture. My problem is that the dax code returns only the first part as you see in the second picture. Below you see my dax code which is taken from a pattern getting the last non blank value. But it only works properly when there is a complete date range with no holes. May be I have misunderstood the function, but I hope any of you can give me a hint on my problem.

 

Regards Geir

 

dax.pngdax_result.png

 

 

In_weight :=
CALCULATE (
SUM ( Production[In Weight] );
FILTER (
Production;
Production[Date Key]
= CALCULATE (
LASTNONBLANK ( Production[Date Key]; 1 );
FILTER ( Production; Production[PDay] <= LASTDATE

( 'Date'[Date] ) )
)
)
)

 

4 REPLIES 4
Super User
Super User

Re: Problem with a dax query

@geirf Could you please post the same sample data in copiable format to replicate your issue and to resolve it quicker.



Did I answer your question? Like and Mark my post as a solution !

Proud to be a Datanaut !





BobBI Member
Member

Re: Problem with a dax query

Hi , Please let us know the desire result you are after and what is pday?

geirf Frequent Visitor
Frequent Visitor

Re: Problem with a dax query

Well, below is a semi column temp data of the part of the fact table. The date dimension data is something most of you have. The measure takes the In Weight column. One thing you could have in mind is that the project key is changing in this data set. That is normal since a new project has started and the old has ended. 

 

Hope this will help

Date Key;Version Key;Company Key;Department Key;Project Key;Product Key;In Number;In Weight
20150331;1;7;11;5;3;0;0
20150430;1;7;11;5;3;984143;109642,7175
20150531;1;7;11;5;3;1485487;228804,9059
20150630;1;7;11;5;3;1462696;315675,2225
20150731;1;7;11;5;3;1455644;418360,9509
20150831;1;7;11;5;3;1453471;653010,3773
20150930;1;7;11;5;3;1441753;979817,0171
20151031;1;7;11;5;3;1429626;1562978,912
20151130;1;7;11;5;3;1414282;2033913,397
20151231;1;7;11;5;3;1407627;2585472,488
20160131;1;7;11;5;3;1404369;2979291,855
20160229;1;7;11;5;3;1401084;3223859,054
20170731;1;7;11;11;3;0;0
20170831;1;7;11;11;3;163463;13771
20170930;1;7;11;11;3;562219;58828
20171031;1;7;11;11;3;760161;124032
20171130;1;7;11;11;3;1533765;307765
20171231;1;7;11;11;3;1516564;460391
20180131;1;7;11;11;3;1501521;637608
20180228;1;7;11;11;3;1487303;833465
20180331;1;7;11;11;3;1476195;1036800
20180430;1;7;11;11;3;1463993;1293025
20180531;1;7;11;11;3;1452340;1598867
20180630;1;7;11;11;3;1440962;2036573
20180731;1;7;11;11;3;1430644;2588696
20180831;1;7;11;11;3;1421775;3253901
20180930;1;7;11;11;3;1412961;3970369
20181031;1;7;11;11;3;1334483;4362122
Community Support Team
Community Support Team

Re: Problem with a dax query

hi, @geirf

After my test based on your formula and the data you provided.

I create a date table and create the relationship with Production table by Date key

 

10.JPG

then create a date column PDay by Date key in Production table

11.JPG

 

then create the measure.

It works well.

12.JPG

here is my pbix, please try it.

https://www.dropbox.com/s/pxgrmio7d3mb6wa/Problem%20with%20a%20dax%20query.pbix?dl=0

 

If it is not your case, please share sample pbix file and expected output . You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

  

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.