Help with Matrix

[akhaliq7]

I would like my matrix to look like the below in Power BI:

Below is my current DAX code:

Admin = 
VAR StartDate = 
CALCULATE(
	STARTOFYEAR('DimDate'[Date]),
	DimDate[Date] = EDATE(TODAY(),-1)
)
VAR EndDate =
CALCULATE(
	ENDOFMONTH(DimDate[Date]),
	DimDate[Date] = EDATE(TODAY(),-1)
)
RETURN
CALCULATE(
    COUNT('Training'[#]),
    'Training'[Department] = "Admin",
	KEEPFILTERS(DimDate[Date] >= StartDate),
	KEEPFILTERS(DimDate[Date] <= EndDate)
)

The problem with above is zeros do not show in the date range specified, also when I  add coalesce(query,0) then zeros are added to every month row, I would only like months jan, feb to show as the current month checks are incomplete, don't want future months to show either, and my date table is from januaury to December 2024.

[v-jingzhan-msft · Accepted solution]

Hi @akhaliq7 

You can add a relative date filter or a relative date slicer to show last 2 months. 

Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

Or try a measure like

Measure = 
IF (
    MONTH ( MAX ( DimDate[Date] ) ) < MONTH ( TODAY () ),
    COALESCE ( [your measure], 0 ),
    BLANK ()
)

BTW, it seems you are computing something like YTD, you may try something simpler for the measure

CALCULATE (
    [measure],
    DATESYTD ( 'Date'[Date] )
)

Basics of Time Intelligence in DAX for Power BI; Year to Date, Quarter to Date, Month to Date - RADA...

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[akhaliq7]

Thanks for your help. I used the relative date filter and will try out the measures later.