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.
Hello,
I want create measure to count my expanses between two dates, in my database I have infomation like this:
Product ID Supplier ID Price per day currency_id Valid_from
12 55 10 2 2020.01.01
12 55 18 2 2020.06.01
12 55 15 2 2020.09.01
2 55 10 2 2020.01.01
2 55 12 2 2020.09.01
1) My task is calculate my expanses depends of date in my Slicer (visual).
Expenses = price per day * days count, so I need days count but price depends of interval for exaample if I select in slicer date from 2020.01.01 to 2020.12.31 need to get = (150 days (from 2020.01.01 to 2020.06.01) * 10)+ 92 days (from 2020.06.01-2020.09.01)*18 ... and so on.
2) I have currency_rates table, I need convert price to EUR (currency id = 2 is USD) by last date of every month. And first task shoud be calculated with converted price.
Sorry guys i'm limited with my english I hope you understand that task is.
Solved! Go to Solution.
Hi @AndrejZevzikov,
Try adding the following measures:
Number_Days
=
VAR MAXIMUM =
MAX ( 'calendar'[Date] )
VAR MINIMUM =
MIN ( 'calendar'[Date] )
VAR temp_table =
FILTER (
ALL ( 'Table'[Product ID], 'Table'[Valid From] ),
'Table'[Valid From] > MAX ( MINIMUM, MIN ( 'Table'[Valid From] ) )
)
VAR Minimum_date =
MINX (
FILTER (
temp_table,
'Table'[Product ID] = SELECTEDVALUE ( 'Table'[Product ID] )
),
'Table'[Valid From]
)
VAR number_days =
DATEDIFF (
MAX ( SELECTEDVALUE ( 'Table'[Valid From] ), MINIMUM ),
IF ( ISBLANK ( Minimum_date ), MAXIMUM, MIN ( Minimum_date, MAXIMUM ) ),
DAY
)
RETURN
IF ( number_days < 0, 0, number_days )
Total_Cost =
VAR temp_table =
SUMMARIZE (
'Table',
'Table'[Product ID],
'Table'[Price per day],
'Table'[Supplier ID],
"Number_Days_calc", [Number_Days]
)
RETURN
SUMX ( temp_table, 'Table'[Price per day] * [Number_Days_calc])
Check Result below and in attach PBIX file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @AndrejZevzikov,
Try adding the following measures:
Number_Days
=
VAR MAXIMUM =
MAX ( 'calendar'[Date] )
VAR MINIMUM =
MIN ( 'calendar'[Date] )
VAR temp_table =
FILTER (
ALL ( 'Table'[Product ID], 'Table'[Valid From] ),
'Table'[Valid From] > MAX ( MINIMUM, MIN ( 'Table'[Valid From] ) )
)
VAR Minimum_date =
MINX (
FILTER (
temp_table,
'Table'[Product ID] = SELECTEDVALUE ( 'Table'[Product ID] )
),
'Table'[Valid From]
)
VAR number_days =
DATEDIFF (
MAX ( SELECTEDVALUE ( 'Table'[Valid From] ), MINIMUM ),
IF ( ISBLANK ( Minimum_date ), MAXIMUM, MIN ( Minimum_date, MAXIMUM ) ),
DAY
)
RETURN
IF ( number_days < 0, 0, number_days )
Total_Cost =
VAR temp_table =
SUMMARIZE (
'Table',
'Table'[Product ID],
'Table'[Price per day],
'Table'[Supplier ID],
"Number_Days_calc", [Number_Days]
)
RETURN
SUMX ( temp_table, 'Table'[Price per day] * [Number_Days_calc])
Check Result below and in attach PBIX file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIt's working, thanks!
But maybe have any ideas about second part :
"2) I have currency_rates table, I need convert price to EUR (currency id = 2 is USD) by last date of every month. And first task shoud be calculated with converted price."
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |