cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Measure from two fact tables depending on a date

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

3 REPLIES 3
Community Support

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.

Community Champion

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

Thank you for the kudos 🙂

Frequent Visitor

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors