Forum Discussion
Access Query to Power BI Table Challenge
Hello, I have a query and a table in Access. I have used a query to produce a simple resultant table. I have connected the query and table with their relationships to Power BI and I am trying...
Can you share the syntax of your measures?
I did the query in Access and have the below table to work with.
| Date_Transfert | Batch_nb | SumOfTonnes_Blast_id | Close_Date | Open_Date | Batch_Colour |
| 2020-07-01 | RomPad3_Finger_Orange_B00059 | 624 | 2020-07-06 | 2020-07-01 | RomPad3_Finger_Orange |
| 2020-07-02 | RomPad3_Finger_Orange_B00059 | 336 | 2020-07-06 | 2020-07-01 | RomPad3_Finger_Orange |
| 2020-07-03 | RomPad3_Finger_Jaune_B00062 | 144 | 2020-07-07 | 2020-07-03 | RomPad3_Finger_Jaune |
| 2020-07-04 | RomPad3_Finger_Gris_B00060 | 624 | 2020-07-07 | 2020-07-03 | RomPad3_Finger_Gris |
| 2020-07-05 | RomPad3_Finger_Gris_B00060 | 240 | 2020-07-07 | 2020-07-03 | RomPad3_Finger_Gris |
| 2020-07-05 | RomPad3_Finger_Jaune_B00062 | 1104 | 2020-07-07 | 2020-07-03 | RomPad3_Finger_Jaune |
| 2020-07-06 | RomPad3_Finger_Jaune_B00062 | 1344 | 2020-07-07 | 2020-07-03 | RomPad3_Finger_Jaune |
Here is my non working measure.
Tonnes = calculate(
sum(qry_Daily_Finger[SumOfTonnes_Blast_id]),
filter(all(qry_Daily_Finger),
qry_Daily_Finger[Open_Date]<[Selected_date]
&& qry_Daily_Finger[Close_Date]>[Selected_date]
&& qry_Daily_Finger[Date_Transfert]<[Selected_date]
&&qry_Daily_Finger[Date_Transfert]<=max(qry_Daily_Finger[Date_Transfert])
)
)
The Selected Date is a measure as follows
Selected_date = format(DATEVALUE([selected_Year] & "-" & [Selected_Month_Abv] & "-" & [Selected_Day_nb2]),"YYYY-MM-DD")
selected_Year = SELECTEDVALUE((date_Tbl[Year]))
Selected_month_2 = month([Selected_date])
Selected_Day_nb2 = SELECTEDVALUE(date_Tbl[Day_nmb_2])