Fact Vs Forecast

[AmitM]

Hi folks,
I wonder if someone did a project where he/she needed to construct a model that checks how the company performed Vs the forecast?
How should I construct the model? Should it be 2 files (actual, forecast) or is there another way?
In addition to that, I have data of prices that will update throughout time + I have the quantities I need to order.
I can order quantities on any day, and the price I will use will be the last known...
So if the price list update was in 12/6/2014 and reservation has been made in the 15/6/2014 then I will use those prices, whereas if the purchase to be made in 10/6/14 I will use the previous price list.
To clarify- please find attached:

Does anyone know how to create the appropriate DAX formula?
Thanks

 

[AmitM]

I got to another barrier:

I want to see which company gave me that price quote.

I used Lookupvale
=LOOKUPVALUE(ISuppliers[Supplier],ISuppliers[Year],Qty[Year],ISuppliers[Item Description],Qty[Description])

That yields the company where the quote match the date of the purchase.

But it can't locate the company that is responsible for the quote if the quote date< purchase date.

unfortunately, using <= can't give me the solution in such format

=LOOKUPVALUE(ISuppliers[Supplier],ISuppliers[Year],<=Qty[Year],ISuppliers[Item Description],Qty[Description])

The other thing I just realized is that if I have several data entries just a match by Year won't do... It needs to be matched by dates

 

[AmitM]

I tried something like that:

=CALCULATE(VALUE(ISuppliers[Supplier]), FILTER(ISuppliers, ISuppliers[Year]<= Qty[Year]), FILTER(ISuppliers, BB[Description] =ISuppliers[Item Description]))

But results in Error. I think that the Calculate with Value can't operate well here...

 

[AmitM]

Calculate(value
can't be done.

I tried to enter an index to the suppliers so I could use the calculate( with an aggregate function (like sum).

'=IF(CALCULATE(sum(ISuppliers[SupplierID]), FILTER(ISuppliers, ISuppliers[Year]= Qty[Year]), FILTER(ISuppliers, Qty[Description] =ISuppliers[Item Description]))<>BLANK(),CALCULATE(sum(ISuppliers[SupplierID]), FILTER(ISuppliers, ISuppliers[Year]=Qty[Year]), FILTER(ISuppliers, Qty[Description] =ISuppliers[Item Description])),CALCULATE(sum(ISuppliers[SupplierID]), FILTER(ISuppliers, ISuppliers[Year]< Qty[Year]), FILTER(ISuppliers, Qty[Description] =ISuppliers[Item Description])))

the problem is in the false section- I aggregate all the years below the year instead of just the last one (and that will give me 1 number that represents the SupplierID)

 

[AmitM]

I Gave up- Can't create a function that checks who was the last supplier that gave the quote.

Any help will be appreciated.

 

[scottsen]

Let's start with this, to get some hard-core learning done. It's much like your measure with calc(value()) by which you MEANT count(values())... but... let's start here:

=CALCULATE(COUNTROWS(ISuppliers), FILTER(ISuppliers, ISuppliers[Year]<= Qty[Year]), FILTER(ISuppliers, BB[Description] =ISuppliers[Item Description]))

 

[AmitM]

Did it.

Will the next step be to retrieve the company name from the "new table" the filters created?

What function can do that?

 

[scottsen]

Well, what I am going for is... what did we learn from the previous calculated column? The COUNTROWS(ISuppliers) is merely telling you exactly how many rows your filter is matching. How many are you "hoping" it matches?

 

[AmitM]

So it did create a new filtered table, which in one of the examples I took to examine, had 5 rows.

The output was 5 (meaning 5 identical "descriptions" and less than the <gs id="2c05f113-13a4-44f8-aa11-404d5061d387" ginger_software_uiphraseguid="5fa49d16-bbd2-4384-ae4c-896870876080" class="GINGER_SOFTWARE_mark">dd</gs>/mm/yy date)

But how can this "new" filtered table identify who was the supplier that is responsible for the quote and import it as a solution?

 

[scottsen]

Realize I have no idea what your Supplier or BB table look like (and still suspect you will be better of reshaping your data in power query or similiar)... and I was just trying to "teach a man to fish", as they say.

I tried something like that:

=CALCULATE(VALUE(ISuppliers[Supplier]), FILTER(ISuppliers, ISuppliers[Year]<= Qty[Year]), FILTER(ISuppliers, BB[Description] =ISuppliers[Item Description]))

This is what I was going off of. Trying to show you that the filter you have defined there, is returning > 1 rows. If you want to find the right supplier... you need to get that count to 1. I don't know how you look at your Qty table to "find" which supplier it is from, you have to tell me...

 

[AmitM]

I see, so the only way Calculate could have <gs id="98eca981-0e6f-4b6b-8c1d-42ee6f06eca9" ginger_software_uiphraseguid="ec9bb616-08f4-4c82-ab4e-4367bd483fe1" class="GINGER_SOFTWARE_mark">result</gs> in a "Supplier Name" is by filtering to 1 option.

So that means I probably need to work on the foundations of the model. Maybe they aren't that solid as I thought.

But can calculate yield a text? Because an aggregation function is needed....

In addition, I think that the star scheme isn't possible in my model as it consists from 4 data tables (and not the "fact sales" table as all demos tend to display).