How to caculate target value with 3 disconencted slicers

[Maradona]

Hi expert,

I have 2 tables: Business Table, Target Table

1. Line and stacked column chart.
2. shared axis: Dim_Date[Date](365 days), using DAX to generate a simple table
3. 4 slicers: Dim_Date[Month], Business Table[Product], Business Table[Category], Business Table[Flag]
4. colum values: Business Table[value]
5. Line values: expceted dynamic measure, caculate Target Table[target value] with 3 slicers (except Dim_Date[Month])
6. Relationships: Dim_Date[Date] 1:* Business Table[Date], Dim_Date[Date] 1:* Target Table[Date]

Both of Business have date column, howerver, they only have partial date date.

In addtion, Target Table[Date] only have the firt day of every month which represent the target value of whole month.

Now, the column values work like a charm.

Basically, I'd like a dynamic line measure with 3 disconencted scliers(except month)

Sample data

Expected outcome

Is there any idea?

Appreciate for your help.

[amitchandak · Accepted solution]

@Maradona , is better to create table for product , category and flag and join them with target and business .

refer: https://www.youtube.com/watch?v=Bkf35Roman8

https://www.seerinteractive.com/blog/join-many-many-power-bi/

Also Create a month end date in traget and join that with date table

Month end date  =eomonth([date],0)

days in month = day([Month end date])

Now you can join date table with, Month end date . Now you can closingbalancemonth

Refer to this blog - Inplace year funtions use month functions closingbalancemonth , datesmtd , totalmtd

PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[Maradona]

@amitchandak

That's a great idea, now using a bridge table, it looks work well.

Thank you very much, have a good day!