Measure to coalesce columns in a matrix visual

[db_2025]

Hello,

I have data in a fact table that looks like the following, with "Version" and "Month" in separate dimension tables:

Version	Month	Sales
Actual	Jan	10000
Actual	Feb	15000
Actual	Mar	12500
Actual	Apr	14000
January Forecast	Jan	9000
January Forecast	Feb	13500
January Forecast	Mar	12000
...	…	...
January Forecast	Dec	16000
February Forecast	Feb	14000
February Forecast	Mar	15000
February Forecast	Apr	16000
...	…	...
February Forecast	Dec	16000
March Forecast	Mar	14000
March Forecast	Apr	15000

I am trying to create a measure so that I can use the sum of sales where Version = "Actual Sales" for previous months combined with the sum of sales from the forecast for current and future months in a matrix visual. I've tried a few different measures but the previous months for the "<month> Forecast" columns always remain blank like this:

Month	Actual Sales	January Forecast	February Forecast	March Forecast	April Forecast	May Forecast
Jan	10000	9000
Feb	15000	13500	14000
Mar	12500	12000	15000	14000
Apr	14000	16000	16000	15000	16000
May		15000	15000	15000	16000	16000
Jun		15000	15000	15000	15000	15000
Jul		15000	15000	15000	15000	15000
Aug		15000	15000	15000	15000	15000
Sep		15000	15000	15000	15000	15000
Oct		16000	16000	16000	15000	15000
Nov		16000	16000	16000	16000	16000
Dec		16000	16000	16000	16000	16000
Total	51500	173500	168000	152000	139000	123000

I've tried using a few measures including the following with no success:

COALESCE(

   Calculate(Sum(FactTable[Sales]), Filter(DimVersion,DimVersion = "Actual Sales")),

   Calculate(Sum(FactTable[Sales])

)

Any help is greatly appreciated. Thanks

[v-pgoloju · Accepted solution]

Hi @db_2025,

Thank you for reaching out to the Microsoft Fabric Forum Community.

And also thanks to @rajendraongole1  for prompt and useful response.

Try using modified Dax measure.

Combined Sales =
VAR SelectedVersion = SELECTEDVALUE(DimVersion[Version])
VAR SelectedMonthNumber = SELECTEDVALUE(DimMonth[MonthNumber])

VAR ForecastBaseMonth =
SWITCH(
TRUE(),
CONTAINSSTRING(SelectedVersion, "January"), 1,
CONTAINSSTRING(SelectedVersion, "February"), 2,
CONTAINSSTRING(SelectedVersion, "March"), 3,
CONTAINSSTRING(SelectedVersion, "April"), 4,
CONTAINSSTRING(SelectedVersion, "May"), 5,
CONTAINSSTRING(SelectedVersion, "June"), 6,
CONTAINSSTRING(SelectedVersion, "July"), 7,
CONTAINSSTRING(SelectedVersion, "August"), 8,
CONTAINSSTRING(SelectedVersion, "September"), 9,
CONTAINSSTRING(SelectedVersion, "October"), 10,
CONTAINSSTRING(SelectedVersion, "November"), 11,
CONTAINSSTRING(SelectedVersion, "December"), 12,
BLANK()
)

RETURN
IF(
SelectedMonthNumber < ForecastBaseMonth,
CALCULATE(
SUM(FactTable[Sales]),
'DimVersion'[Version] = "Actual",
REMOVEFILTERS('DimVersion') 
),
CALCULATE(
SUM(FactTable[Sales]),
'DimVersion'[Version] = SelectedVersion
)
)

If this solution helped, please consider marking the response as accepted and giving it a thumbs-up so others can benefit as well.

Best regards,
Prasanna Kumar

[db_2025]

Thank you, this did the trick. And thank you to @rajendraongole1 for your contribution as well!

Edit: I noticed an issue. The column totals in the matrix are not correct. They are adding up as the total of the Actual Sales only. Any thoughts on why this might be happening?

Edit2: I was able to make the total row calculate correctly by modifying the measure as follows - 

...

RETURN
IF(

HASONEFILTER(DimMonth[MonthNumber]),

IF(
SelectedMonthNumber < ForecastBaseMonth,
CALCULATE(
SUM(FactTable[Sales]),
'DimVersion'[Version] = "Actual",
REMOVEFILTERS('DimVersion') 
),
CALCULATE(
SUM(FactTable[Sales]),
'DimVersion'[Version] = SelectedVersion
)

),

CALCULATE(
SUM(FactTable[Sales]),
'DimVersion'[Version] = "Actual",
REMOVEFILTERS('DimVersion') , 
Selected Month < ForecastDate

) +

CALCULATE(
SUM(FactTable[Sales]),
'DimVersion'[Version] = SelectedVersion
)

)

[rajendraongole1]

Hi @db_2025  - You're on the right track conceptually ,use a Date table with actual dates and use MONTH([Date]).

create a measure for combined monthly sales as below:

Referece:

Combined Sales =
VAR SelectedVersion = SELECTEDVALUE(DimVersion[Version])
VAR SelectedMonthNumber = SELECTEDVALUE(DimMonth[MonthNumber])

-- Pull the forecast base month out of the version name
VAR ForecastBaseMonth =
SWITCH(TRUE(),
CONTAINSSTRING(SelectedVersion, "January"), 1,
CONTAINSSTRING(SelectedVersion, "February"), 2,
CONTAINSSTRING(SelectedVersion, "March"), 3,
CONTAINSSTRING(SelectedVersion, "April"), 4,
CONTAINSSTRING(SelectedVersion, "May"), 5,
CONTAINSSTRING(SelectedVersion, "June"), 6,
CONTAINSSTRING(SelectedVersion, "July"), 7,
CONTAINSSTRING(SelectedVersion, "August"), 8,
CONTAINSSTRING(SelectedVersion, "September"), 9,
CONTAINSSTRING(SelectedVersion, "October"), 10,
CONTAINSSTRING(SelectedVersion, "November"), 11,
CONTAINSSTRING(SelectedVersion, "December"), 12,
BLANK()
)

-- Choose Actuals before the forecast month, Forecast from forecast month onwards
RETURN
IF(
SelectedMonthNumber < ForecastBaseMonth,
CALCULATE(
SUM(FactTable[Sales]),
DimVersion[Version] = "Actual"
),
CALCULATE(
SUM(FactTable[Sales]),
DimVersion[Version] = SelectedVersion
)
)

Hope this helps , please check

[db_2025]

Thanks for your reply. I have tested this and its is not working unfortunately. The logic part of it comparing SelectedMonthNumber to ForecastBaseMonth works fine but I still end up with the blanks for the prior months rather than the Actual Sales. Do you think I need a REMOVEFILTERS or something like that? I think the matrix is taking precedent over the measure's filtering.