SUM of Columns in a Matrix - Help with DAX

[MichaelStrauss]

Hi all,

I have one fact table with the following data:

Each month a forecast is created for the upcoming 12 months. The forecast versions are named 301 (created in January), 302 (Created in February) and so on ..

That means that for each month, there are 12 versions that predicted it (some from last year and some in that year). Furthermore the actual sales value of the predicted month is stored in a version called"#". See table below as an example

Forecast Version [Version_Key] in Dax	[Forecast_Creation_Year]	Predicted Month_Year [Month_Year] in Dax	Forecast  [Forecast_Finance]	Sales of predicted month [Sales] in Dax
301	2021	Mar-21	1.1M
302	2021	Mar-21	0.9M
303	2021	Mar-21	1.11M
304	2020	Mar-21	1.1M
305	2020	Mar-21	0.9M
306	2020	Mar-21	1.11M
307	2020	Mar-21	1.1M
308	2020	Mar-21	0.9M
309	2020	Mar-21	1.11M
310	2020	Mar-21	1.1M
311	2020	Mar-21	0.9M
312	2020	Mar-21	1.11M
#		Mar-21		1M

Target: User can set the forecast creation year (single value) and the range of dates (the predicted months - must be in same calendar year meaning months in 2021 only or 2022 only) he would like to see. 

I've built a DAX measure for a matrix to show:

Forecast - If a forecast was created within that year (In yellow)

Sales - Otherwise (In Green)

Variables:

VAR SALES = CALCULATE(SUMX('Fact Table',[Sales]),'Fact Table'[Version_key] = "#")

VAR FORECAST_CREATED_AT_CHOSEN_YEAR = CALCULATE(SUMX('Fact Table','Fact Table'[Forecast_Finance]),'Fact Table'[Forecast_Creation_Year] = SELECTEDVALUE('Table'[FORECAST CREATION YEAR]))
-- 'Table' is a table with the parameter of the forecast creation year (I had to make this filter so as to not ruin other stuff)

The Measure:

IF(ISBLANK(FORECAST_CREATED_AT_CHOSEN_YEAR),

SALES,

FORECAST_CREATED_AT_CHOSEN_YEAR))

It works great, but I can't figure out a way to put the same logic to the TOTAL column, because it actually loses the filter of the headers (predicted month), and the versions too (as the sales are stored in a special version - "#", without details about creation year)

I had encountered the problem of TOTAL column in the past and used 

IF(HASONEVALUE('Fact Table'[Month_Year]), Presented_Measure, .....)

But I can't figure a DAX formula for this case for the TOTAL column

[aj1973]

Hi @MichaelStrauss 

You need to add a Date Table to your model in order to use Time Intelligence.

[MichaelStrauss]

Hi @aj1973 ,
Say I have a date Table, what would be the relation between the tables? I thought about this idea but did not see how it may help me. I have lots of other measures in my dashboard that might be affected by this, that's why I'm looking for a specific solution to my last problem 🙂

[aj1973]

what you call a "Fact" table doesn't look like it is! It is more like a manually made table and it is  missing the primary key that would be more like a Date/Time column.

[MichaelStrauss]

That's true, it's not an exact fact table, however it's the data we have available

The [Month_Year] column is the date column

[aj1973]

Therefore it is not going to work as you want. 

Why don't work directly with the source, do you need help with that?