Forum Discussion
Calendar Table Not Functioning Properly
- 7 years ago
Use these measures:
Gross Billings $ FY 2018 = CALCULATE( [Gross Billings $], FILTER('Calendar','Calendar'[Year] = 2018) )And for the MTD:
Gross Billings $ FY18 MTD = CALCULATE( [Gross Billings $], DATESMTD('Calendar'[Date]), FILTER('Calendar','Calendar'[Year] = 2018) )The reason it works that way is when you use a simple filter in CALCULATE as you have, it wraps an ALL() around it. Read this SQLBI article on how simple filters in CALCULATE work.
The crux of it is your measure:Test = CALCULATE( [Gross Billings $], 'Calendar'[Year] = 2018 )is rewritten as this by DAX:
Test = CALCULATE( [Gross Billings $], FILTER( ALL('Calendar'[Year]), 'Calendar'[Year] = 2018 ) )So viewed that way, it is saying ignore the filter context from the matrix columns. Don't use just 2016 dates for column 2016, but use all dates, but then only show me 2018 from that. Then it does the same for 2017, 2018, 2019, etc. So only 2018 looks right to you.
I rarely use a simple filter in Calculate. I almost always use FILTER() there to ensure I have more control over what I get.
Use these measures:
Gross Billings $ FY 2018 =
CALCULATE(
[Gross Billings $],
FILTER('Calendar','Calendar'[Year] = 2018)
)And for the MTD:
Gross Billings $ FY18 MTD =
CALCULATE(
[Gross Billings $],
DATESMTD('Calendar'[Date]),
FILTER('Calendar','Calendar'[Year] = 2018)
)The reason it works that way is when you use a simple filter in CALCULATE as you have, it wraps an ALL() around it. Read this SQLBI article on how simple filters in CALCULATE work.
The crux of it is your measure:
Test =
CALCULATE(
[Gross Billings $],
'Calendar'[Year] = 2018
)is rewritten as this by DAX:
Test =
CALCULATE(
[Gross Billings $],
FILTER(
ALL('Calendar'[Year]),
'Calendar'[Year] = 2018
)
)So viewed that way, it is saying ignore the filter context from the matrix columns. Don't use just 2016 dates for column 2016, but use all dates, but then only show me 2018 from that. Then it does the same for 2017, 2018, 2019, etc. So only 2018 looks right to you.
I rarely use a simple filter in Calculate. I almost always use FILTER() there to ensure I have more control over what I get.
edhans,
If I could give you a e-highfive & e-chestbump I would.
Appreciate your sample PBIX, helped me recreate my problem for you.
Thank you very much, you are a gentleman and a scholar.
<michael-scott.gif>