Hi @jj1 ,
For Item 1, where you need to return the value when Date Created is in January 2024 but Date Mailed is not in January 2024, the best approach is to work with a proper date table. First, create a date table covering the relevant range:
DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Year-Month", FORMAT([Date], "YYYY-MM")
)
This date table can be used to build slicers and ensure consistent filtering across multiple tables. Although in this case, you're comparing two separate date columns in the same table, the measure itself can still use the logic with formatted values:
Measure_Jan2024_Created_NotMailed :=
CALCULATE(
SUM(Sample[Measure]),
FILTER(
Sample,
FORMAT(Sample[Date Created], "YYYY-MM") = "2024-01"
&& FORMAT(Sample[Date Mailed], "YYYY-MM") <> "2024-01"
)
)
This measure will return the sum of Measure where the Date Created falls in January 2024 but the Date Mailed does not.
For Item 2, where selecting a month should return both the Sales value from Table1 and the Refund value from Table2 for that month, it is better to use a shared DateTable rather than a disconnected month list. After creating the DateTable, set up relationships from DateTable[Date] to both Table1[Date] and Table2[Date].
Then, instead of using TREATAS, you can simply create measures that leverage the filter context provided by slicers based on the DateTable:
Sales_Selected :=
CALCULATE(
SUM(Table1[Sales])
)
Refund_Selected :=
CALCULATE(
SUM(Table2[Refund])
)
These measures will both respond to any filters applied via the DateTable, such as a slicer using DateTable[Year-Month]. This approach is more scalable and avoids issues with inconsistent sorting or ambiguous text-based month matching, making it much more suitable for professional, production-grade Power BI models.
Best regards,
