Forum Discussion

Mario_Archontis's avatar
Mario_Archontis
New Member
8 months ago
Solved

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

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 Rev...
Need Help
  • danextian's avatar
    danextian
    8 months ago

    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.

amitchandak's avatar
amitchandak
Super User
8 months ago

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]))