Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
73 | |
70 | |
38 | |
23 | |
23 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |