Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
I am learning Power BI DAX, and would like clarification on how to use Calculate() with filter on a column.
I created a new data query table and relationship called "Dates" to define CurrentMonth as true/false. There is also CurrentYear, CurrentWeek, CurrentDay, etc, which gives flexibility for start of week/start of month in the event of a holiday, etc.
Here's my incorrect formula, which results in "0" in the report display field:
Gross Margin % MTD Update = Calculate(
[Total Gross Margin %],
FILTER(Dates,Dates[CurrentMonth]=TRUE)
)
Previously, I was using TOTALMTD, which works. Note that the table Date is different, but both contain the same information:
Gross Margin % MTD = TOTALMTD(
[Total Gross Margin %],
FILTER(ALL('Date'[Date]),'Date'[Date] <= TODAY())
)How can I update the first formula above?
Solved! Go to Solution.
Turns out, Filter wasn't a requirement here. The below returned the expected value filtered just fine: (DAX 101, but it helps to know)
Gross Margin % MTD = CALCULATE(
[Total Gross Margin %],'dimCalendar'[CurrentMonth] = TRUE()
)The label for this field was customized to the following, if anyone finds it useful:
Month-to-date =
FORMAT(EDATE(TODAY(),0),"mmm mm/01 - ") &
FORMAT(TODAY(),"mmm mm/dd")This returns Mar 03/01 - Mar 03/27.
Turns out, Filter wasn't a requirement here. The below returned the expected value filtered just fine: (DAX 101, but it helps to know)
Gross Margin % MTD = CALCULATE(
[Total Gross Margin %],'dimCalendar'[CurrentMonth] = TRUE()
)The label for this field was customized to the following, if anyone finds it useful:
Month-to-date =
FORMAT(EDATE(TODAY(),0),"mmm mm/01 - ") &
FORMAT(TODAY(),"mmm mm/dd")This returns Mar 03/01 - Mar 03/27.
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
| User | Count |
|---|---|
| 38 | |
| 37 | |
| 34 | |
| 31 | |
| 27 |
| User | Count |
|---|---|
| 136 | |
| 99 | |
| 73 | |
| 66 | |
| 65 |