Measure from two fact tables depending on a date

[dljdavid]

Hello there!

Hope somebody can help me with this 🙂

I have 2 fact tables:

Foreseen production table:

Date	Reservation ID	Quantity	Amount
01/01/2020	10	1	200
01/01/2020	11	1	100
02/01/2020	12	1	120
03/01/2020	13	1	100
04/01/2020	14	1	100
05/02/2020	15	1	100

Production done table:

Date	Reservation ID	Quantity	Amount
01/01/2020	10	1	200
01/01/2020	11	1	100
02/01/2020	12	1	120

Imagine today is 03/01/2020

Foreseen production table can have records for all dates (past and future).

Production done table only can have records for past dates, as is production already done. 

I want a measure that calculates the total sum of quantity and amount, but filtered on the date selected, like this:

So if I select the date 03/01/2020 I should get:

Date	Reservation ID	Quantity	Amount	Table
01/01/2020	10	1	200	Produced
01/01/2020	11	1	100	Produced
02/01/2020	12	1	120	Produced
03/01/2020	13	1	100	Foreseen
04/01/2020	14	1	100	Foreseen
05/02/2020	15	1	100	Foreseen

I need to discard the records in the foreseen as the date is already produced.

I hope my example is clear, thanks in advance for your help!

David

[v-xicai]

Hi @dljdavid ,

Firstly, you may create relationship between the two tables above, then create column like DAX below.

Table= IF('Foreseen production'[Date]=RELATED('Production'[Date]), "Produced", "Foreseen")

Secondly, create a calendar table without relationship with your fact data table, and let the Calendar[Date] as the source of Slicer visual.

Calendar= CALENDARAUTO()

Finally, you can create measure Filter1 like DAX below, put the Filter1 in the Visual Level Filter of Table visual which is displaying all fields of table 'Foreseen production' , setting Filter1 as "is not blank". This way to discard the produced records in the foreseen table by changing Date slicer.

Filter1 =IF('Foreseen production'[Date]> SELECTEDVALUE(Calendar[Date]) , 1, BLANK())

Best Regards,

Amy 

Community Support Team _ Amy

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

[Stachu]

the easiest way would be to append the 2 tables to something like this:

DateReservation IDQuantityAmountType

01 January 2020	10	1	200	Foreseen
01 January 2020	11	1	100	Foreseen
02 January 2020	12	1	120	Foreseen
03 January 2020	13	1	100	Foreseen
04 January 2020	14	1	100	Foreseen
05 February 2020	15	1	100	Foreseen
01 January 2020	10	1	200	Done
01 January 2020	11	1	100	Done
02 January 2020	12	1	120	Done

but it can be achieved with your setup as well, as long as you add Calendar and Reservation Id tables, joined with both fact tables:

you can then use:

SelectedDate = SELECTEDVALUE('Calendar'[Date])

and

Amount = 
VAR __SelectedDate = [SelectedDate]
VAR __ForeseenQuantity = CALCULATE(SUM('Foreseen production'[Amount]),'Calendar'[Date]>=__SelectedDate)
VAR __DoneQuantity = CALCULATE(SUM('Production done'[Amount]),'Calendar'[Date]<__SelectedDate)
RETURN
__ForeseenQuantity+__DoneQuantity

which gives:

The Reservation Id and Date are coming from dimension tables, not fact tables

[dljdavid]

Thank you @Stachu 

It works as described!

But I would like to add a little bit more of complexity to my example, sorry if I'm "too newbie" 🙂

So we have a selected date, but I want to see the amounts produced for a month inside a year, for an example like this:

Date	Reservation ID	Amount	Quantity	Type
31/12/2019	9	100	1	Produced
01/01/2020	10	100	1	Produced

Date	Reservation ID	Amount	Quantity	Type
31/12/2019	9	100	1	Foreseen
01/01/2020	10	100	1	Foreseen
02/01/2020	11	100	1	Foreseen
01/02/2020	12	100	1	Foreseen

So if I select the date of 02/01/20 I would like to see this for year 2020:

Month/year	Amount	Quantity
January  2020	200 (100 produced + 100 foreseen)	2
February 2020	100 (foreseen)	1

Please note that year 2019 is discarded, as I want to see only 2020.

Thanks everybody for your time and help, I really appreciate it!

David