formula

[Julier]

I am looking for a formula to return a vol that matches the date 01/08/25 and the ID BEL_14667/0 from the table below when forecast A matches forecast b

	BEL_14667/0
01/08/2025	#NAME?
ID	forecast A	forecast b	Vol
BEL_14667/0	01/07/2025	01/07/2025	232,932
BEL_14667/0	01/07/2025	01/08/2025	195,613
BEL_14667/0	01/07/2025	01/09/2025	239,887
BEL_14667/0	01/07/2025	01/10/2025	294,476
BEL_14667/0	01/07/2025	01/11/2025	327,610
BEL_14667/0	01/07/2025	01/12/2025	149,035
BEL_14667/0	01/08/2025	01/08/2025	220,000
BEL_14667/0	01/08/2025	01/09/2025	239,887
BEL_14667/0	01/08/2025	01/10/2025	294,476

[grazitti_sapna · Accepted solution]

Hi @Julier,

Try below DAX:-

Vol_Match =
CALCULATE(
MAX(Table[Vol]),
Table[ID] = "BEL_14667/0",
Table[forecast A] = Table[forecast b],
Table[forecast b] = DATE(2025,8,1)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[V-yubandi-msft]

Hi @Julier ,

I wanted to check if you had the opportunity to review the information provided by @AmiraBedh,  @grazitti_sapna . Please feel free to contact us if you have any further questions. 
 

Thank you and continue using Microsoft Fabric Community Forum.

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on Fabric community.

You can filter the table to just the rows where ID = BEL_14667/0 and forecastA = forecastB = #date(2025,8,1)
and from that filtered table, take the first match vol value if no row matches return null.

= let
    Source =
        #table(
            type table [ID = text, forecastA = date, forecastB = date, Vol = number],
            {
                {"BEL_14667/0", #date(2025, 7, 1), #date(2025, 7, 1), 232932},
                {"BEL_14667/0", #date(2025, 7, 1), #date(2025, 8, 1), 195613},
                {"BEL_14667/0", #date(2025, 7, 1), #date(2025, 9, 1), 239887},
                {"BEL_14667/0", #date(2025,10, 1), #date(2025,10, 1), 294476},
                {"BEL_14667/0", #date(2025,11, 1), #date(2025,11, 1), 327610},
                {"BEL_14667/0", #date(2025,12, 1), #date(2025,12, 1), 149035},
                {"BEL_14667/0", #date(2025, 8, 1), #date(2025, 8, 1), 220000},
                {"BEL_14667/0", #date(2025, 8, 1), #date(2025, 9, 1), 239887},
                {"BEL_14667/0", #date(2025, 8, 1), #date(2025,10, 1), 294476}
            }
        ),

    TargetID   = "BEL_14667/0",
    TargetDate = #date(2025, 8, 1),

    Filtered = Table.SelectRows(
        Source,
        each [ID] = TargetID and [forecastA] = TargetDate and [forecastB] = TargetDate
    ),

    ResultVol = if Table.IsEmpty(Filtered) then null else Filtered{0}[Vol]
in
    ResultVol

you can find the solution in the pbix file.

[grazitti_sapna · Accepted solution]

Hi @Julier,

Try below DAX:-

Vol_Match =
CALCULATE(
MAX(Table[Vol]),
Table[ID] = "BEL_14667/0",
Table[forecast A] = Table[forecast b],
Table[forecast b] = DATE(2025,8,1)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!