cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

count days when you have only start date

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.

1 ACCEPTED SOLUTION
Super User

``````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ês

2 REPLIES 2
Super User

``````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ês

Helper I

It'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."

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.