Matrix visual, MoM values shown as zero (Power BI desktop)

[Mario_Archontis]

Hello all.

I have been dealing with this glitch the whole day and neither chatGPT nor Gemini could solve it.

Ok, so I have a dataset (a_Initial_Data) with an Order_Date (Date type) column and a Reveue (Decimal--> Currency type) column amongst others.

I created a DateTime table (with columns: Date, Day Of Week, Month Name, Month Number, Month Sort, Year), marked the Date column as date table AND sorted Month Name by Month Sort.

I created the Relationship, DateTime[Date] to a_InitialData[OrderDate], 1 to many, single direction, and made sure the dates are of the same type (Short date)

Both chatGPT and Gemini suggested the DAX as follows:

Created a Matrix visual, added to Row the DateTable[Year] and the DateTable[Month Name] and to Values the a_Initial_Date[Revenue] and the above measure. Revenue shows correct and MoM as $0.00

Any deas?

[Mario_Archontis]

@amitchandak thanks for the quick reply! Sorry if I do not reply sufficiently on your suggestions, but I am new to this. Anyway,

I do not need join date (extra table with unique dates) but without it (relying on 'a_Initial_Data[Order_date] I failed again.
I need Month to Month for the same year. The dataset is only for 2023.
I do not understand MTD (probably MonthToDate?). If yes, no need also.
I will try with inactive join...

[danextian · Accepted solution]

Hi @Mario_Archontis 

I created the Relationship, DateTime[Date] to a_InitialData[OrderDate], 1 to many, single direction, and made sure the dates are of the same type (Short date)

You mean the columns have the same data type or simply have the same format? Make sure they're both type date, no either  type date or datetime. 

That aside, the main reason you’re getting an incorrect MoM value is that you’re using one variable inside another while trying to change its filter context. Variables are immutable, so the first two variables will return the same results. Instead of reusing CurrentMonthValue in the second variable, use the SUM expression directly.

[Mario_Archontis]

Unbelievable! It was the VAR all along!!!!! Thanks!!!!

[Mario_Archontis]

@danextian thanks for your quick reply.

Columns should!!! have the same data type, and format. Original .csv has Order_date values as 2023-01-01 and the DateTable was based on this field...
Variables, I will try your idea of course, but as an amateur Visual Basic programmer, I find it difficult to this being the case. I would be very happy if it is though😀

[amitchandak]

@Mario_Archontis , seem correct if need based on join date , make sure you use date table in slcier, measure and visual 

Try like  
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month ))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

MTD or month level 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

With inactive join 
calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),DATESMTD('Date'[Date]))