Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dljdavid
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:

DateReservation IDQuantityAmount
01/01/2020101200
01/01/2020111100
02/01/2020121120
03/01/2020131100
04/01/2020141100
05/02/2020151100

 

Production done table:

DateReservation IDQuantityAmount
01/01/2020101200
01/01/2020111100
02/01/2020121120

 

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:

DateReservation IDQuantityAmountTable
01/01/2020101200Produced
01/01/2020111100Produced
02/01/2020121120Produced
03/01/2020131100Foreseen
04/01/2020141100Foreseen
05/02/2020151100Foreseen

 

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
v-xicai
Community Support
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())

 

Filter1 not blank.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
Community Champion
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:

Capture.PNG

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:

Capture.PNG

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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:

 

DateReservation IDAmountQuantityType
31/12/201991001Produced
01/01/2020101001Produced

 

DateReservation IDAmountQuantityType
31/12/201991001Foreseen
01/01/2020101001Foreseen
02/01/2020111001Foreseen
01/02/2020121001Foreseen

 

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

 

Month/yearAmountQuantity
January  2020200 (100 produced + 100 foreseen)2
February 2020100 (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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.