Problem with a dax query

[Anonymous]

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

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] ) )
)
)
)

[Anonymous]

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

[v-lili6-msft]

hi, @Anonymous

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

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

then create the measure.

It works well.

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

[BobBI]

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

[PattemManohar]

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