Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
You want to return a value only if Date Created is in Jan 2024 and Date Mailed is not in Jan 2024.
Assume your table is named 'Sample', and the value to return is in the Measure column.
DAX Measure:
MailedAfterCreatedMonth =
CALCULATE (
SUM ( Sample[Measure] ),
FILTER (
Sample,
FORMAT ( Sample[Date Created], "YYYY-MM" ) = "2024-01" &&
FORMAT ( Sample[Date Mailed], "YYYY-MM" ) <> "2024-01"
)
)
This will help you to Filters only rows where:
Date Created is in Jan 2024, and
Date Mailed is not in Jan 2024.
Returns the sum of Measure for those rows.
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,
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,
You want to return a value only if Date Created is in Jan 2024 and Date Mailed is not in Jan 2024.
Assume your table is named 'Sample', and the value to return is in the Measure column.
DAX Measure:
MailedAfterCreatedMonth =
CALCULATE (
SUM ( Sample[Measure] ),
FILTER (
Sample,
FORMAT ( Sample[Date Created], "YYYY-MM" ) = "2024-01" &&
FORMAT ( Sample[Date Mailed], "YYYY-MM" ) <> "2024-01"
)
)
This will help you to Filters only rows where:
Date Created is in Jan 2024, and
Date Mailed is not in Jan 2024.
Returns the sum of Measure for those rows.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
69 | |
55 | |
38 | |
35 |
User | Count |
---|---|
87 | |
69 | |
59 | |
46 | |
46 |