Hi,
Here is my solution for your problem:
1. Create a new table with only the City to connect with the other two tables
2. Use this measure:
Sum with Cond =
VAR _CurrDate = MAX(Table_B[Date])
VAR _Sum =
CALCULATE(
SUM(Table_A[Qty]),
FILTER(
Table_A,
Table_A[Date1]<_CurrDate && Table_A[Date2] >= _CurrDate
)
)
VAR _Result =
SWITCH(
TRUE(),
NOT(ISBLANK(_CurrDate)) && _Sum=0,"-",
NOT(ISBLANK(_CurrDate)),_Sum
)
RETURN
_Result
And on your table put this fields:
City from the new table, Date from the Table B and the Sum With Cond measure. Like this:
The final result would be this:
If you need any help please let me know.
Hi,
Here is my solution for your problem:
1. Create a new table with only the City to connect with the other two tables
2. Use this measure:
Sum with Cond =
VAR _CurrDate = MAX(Table_B[Date])
VAR _Sum =
CALCULATE(
SUM(Table_A[Qty]),
FILTER(
Table_A,
Table_A[Date1]<_CurrDate && Table_A[Date2] >= _CurrDate
)
)
VAR _Result =
SWITCH(
TRUE(),
NOT(ISBLANK(_CurrDate)) && _Sum=0,"-",
NOT(ISBLANK(_CurrDate)),_Sum
)
RETURN
_Result
And on your table put this fields:
City from the new table, Date from the Table B and the Sum With Cond measure. Like this:
The final result would be this:
If you need any help please let me know.
Helpful resources
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.
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Power BI Monthly Update - May 2024
Check out the May 2024 Power BI update to learn about new features.
