Month over Month Measure Error with data model

[Anonymous]

Hi,

I'm using the following measure to calculate the amount of sales month over month:

SalesLastMonth =

CALCULATE([SALES],

                    PARALLELPERIOD(

                    FactSales[OrderDate].[Date], -1, MONTH)

                    )

This works fine, however if I add a DimCalender table to my FactSales table and make a relation on the 'Date Column', the measure does not work anymore. I get this error message: "the column reference in the table cannot be used for variant date, because it does not contain a variant." If I change the date to my Dim_Calender date column in the measure it won't work either. Does anyone know how to fix this? I need the relationship with my DIM_Calender to my FactSales table.

[amitchandak · Accepted solution]

@Anonymous , First of all you have to use a date table that should join with date with time in your fact

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

or

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]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(Sales[Sales Amount]),previousmonth('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.