Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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.
User | Count |
---|---|
84 | |
79 | |
69 | |
46 | |
43 |
User | Count |
---|---|
106 | |
50 | |
49 | |
40 | |
39 |