How to lookup/calulcate for every day (date)

[lk01]

Hi,

let's hope someone can help me 🙂
I got the following table (simplified)

Cust-Id	Prod-ID	Valid From	Value
001	0012	01.01.2022	15,5
001	0012	01.06.2022	14,9
001	0013	01.07.2022	22,1
001	0014	30.06.2018	7,9
002	0015	01.04.2020	14,4
002	0016	01.05.2020	20
002	0016	01.07.2022	44,8
003	0017	15.04.2019	32,15
003	0018	01.07.2021	8,6
003	0019	01.02.2020	15,7
003	0019	01.04.2022	16,2
	...

Values for each product-id which are valid_from a certain date to a newer valid_from date    (sorry for the english)
How can I get to a table or measure (dax?) and arrive here:?

date	Prod-ID	Value
01.01.2022	0012	15,5
01.01.2022	0013	22,1
01.01.2022	0014	7,9
01.01.2022	0015	14,4
01.01.2022	0016	20
01.01.2022	0017	32,15
01.01.2022	0018	8,6
01.01.2022	0019	15,7
02.01.2022	0012	15,5
02.01.2022	0013	22,1
02.01.2022	0014	7,9
02.01.2022	0015	14,4
02.01.2022	0016	20
02.01.2022	0017	32,15
02.01.2022	0018	8,6
02.01.2022	0019	15,7
…
01.04.2022	0012	15,5
01.04.2022	0013	22,1
01.04.2022	0014	7,9
01.04.2022	0015	14,4
01.04.2022	0016	20
01.04.2022	0017	32,15
01.04.2022	0018	8,6
01.04.2022	0019	16,2

The highlighted value is just for clarification what should happen

It doesn't have to be a separate table.
I need to calculate the value for each prod-id in a certain period
f.e..   

from	to	prod-id	value (sum)
01.01.2022	31.01.2022	0012	480,5

Minus saturdays and sundays if possible 😉

I'm relatively new to Power BI and can't wrap my head around this atm

Help would really be appreciated, thank you.

[v-easonf-msft · Accepted solution]

Hi, @lk01 

I still have some doubts about your expected results.

On January 1, 2022, the value corresponding to Prod-ID=0013 should be empty rather than 22,1, right?

If so, I recommend creating two separate dimension tables: “Date” table and "Prod" table.

Calculated  table：

Date = CALENDAR(DATE(2022,1,1),DATE(2022,4,1) )

Prod = VALUES(Table1[Prod-ID]) 

Then try measure  like:

Valid date = CALCULATE(MAX(Table1[Valid From]),FILTER(Table1,Table1[Valid From]<=MAX('Date'[Date])&&Table1[Prod-ID]=MAX(Prod[Prod-ID])))

Value = CALCULATE(MAX(Table1[Value]),FILTER('Table1',Table1[Valid From]=[Valid date]&&Table1[Prod-ID]=MAX(Prod[Prod-ID])))

final result = SUMX(VALUES('Date'[Date]),[Value]) 

Best Regards,
Community Support Team _ Eason

[lk01]

Thank you for your efforts, this is looking better already! 🙂

Yes, that was a typo on my part .. it should be empty in this case, though the real data-table has valid_from dates that go way back.

Still trying to figure out how you did it, but I take what I can get 😉

One more question: trying to group the values by Cust-Id does not work correctly, even when I add cust-id to the Prod table    ( SUMMARIZE(Table1,Table1[Prod-ID],Table1[Cust-Id]) )

Do I need a new measure for this?

[v-easonf-msft]

Hi, @lk01 

Yes. You may need to replace [Prod-id] with [Cust-Id] in all my original measures.

Best Regards,
Community Support Team _ Eason