Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm using Power BI desktop working on a car reporting task, and have been struggling with the following problem for days. I'm quite new to Power BI and have very basic DAX knowledge.
Here are the tables (relevant columns):
REFUEL
Date | Time | Registration Number | Refuel (l) |
2017.02.28 | 10:48 | AAA001 | 51,11 |
2017.03.03 | 07:39 | AAA001 | 40,18 |
2017.03.04 | 10:03 | AAA001 | 46,46 |
2017.03.09 | 09:46 | AAA001 | 36,45 |
2017.03.14 | 18:35 | AAA001 | 37,09 |
2017.03.16 | 19:53 | AAA001 | 52,15 |
2017.03.21 | 09:07 | AAA001 | 31,62 |
2017.03.23 | 07:48 | AAA001 | 45,26 |
2017.03.27 | 08:48 | AAA001 | 48,87 |
2017.04.02 | 16:14 | AAA001 | 49,06 |
Distance:
Date | Time | Registration Number | Distance(km) |
2017.02.28 | AAA001 | 120 | |
2017.02.29 | AAA001 | 160 | |
2017.03.03 | AAA001 | 230 | |
2017.03.04 | AAA001 | 320 | |
2017.03.08 | AAA001 | 190 | |
2017.03.09 | AAA001 | 270 | |
2017.03.12 | AAA001 | 370 | |
2017.03.14 | AAA001 | 400 | |
2016.03.16 | AAA001 | 280 | |
2017.03.20 | AAA001 | 340 | |
2017.03.21 | AAA001 | 350 | |
2017.03.22 | AAA001 | 230 | |
2017.03.23 | AAA001 | 250 | |
2017.03.25 | AAA001 | 410 | |
2017.03.27 | AAA001 | 370 | |
2017.04.01 | AAA001 | 390 | |
2017.04.02 | AAA001 | 240 |
I'd like to sum the distance in this way:
refueling on the closest day to the last day of the month --> its the firtst day i want to sum (02.28 - 120 km)
to the refueling on the closest day to the day of the next month --> its the last day I want to sum (04.02 - 240 km)
Could anyone please give me any hint how to achieve this?
Thanks!
Hi @saska10,
It's difficult to reproduce your scenario based on your description, please list your expected result from the sample data sample. So that we can provide the solution which is close to your requirement. Thanks for understanding.
Best Regards,
Angelia
Hi @v-huizhn-msft,
I want a result table like this:
Date | Registration Number | Distance |
2017.02.28 | AAA001 | 120 |
2017.02.29 | AAA001 | 160 |
2017.03.03 | AAA001 | 230 |
2017.03.04 | AAA001 | 320 |
2017.03.08 | AAA001 | 190 |
2017.03.09 | AAA001 | 270 |
2017.03.12 | AAA001 | 370 |
2017.03.14 | AAA001 | 400 |
2016.03.16 | AAA001 | 280 |
2017.03.20 | AAA001 | 340 |
2017.03.21 | AAA001 | 350 |
2017.03.22 | AAA001 | 230 |
2017.03.23 | AAA001 | 250 |
2017.03.25 | AAA001 | 410 |
2017.03.27 | AAA001 | 370 |
2017.04.01 | AAA001 | 390 |
2017.04.02 | AAA001 | 240 |
All in all, the most important thing that the result table containts: from the day of refueling closest to the new month till the next new month's closest day of refueling.
Thank you for your help!
Hi @saska10,
For this result table, you can click New table under Modeling on home page, type the following formula.
result-table=SELECTCOLUMNS(Distance,"Date",Distance[Date],"Registration Number",Distance[Registration Number],"Distiance",Distance[Distance])
Best Regards,
Angelia
Can you explain why 3.27 is not the day that you want instead of 4.02?
I'm guessing that you would need to have a central date table involved here and relate both refuel and distance tables to it. In theory, you could then create a measure that uses ENDOFMONTH in a context that will achieve what you want.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |