count days when you have only start date

[AndrejZevzikov]

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.

[MFelix · Accepted 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:

[AndrejZevzikov]

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