Forum Discussion
Using Calculate with Filter on Current Month Column
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?
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.
1 Reply
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.