Forum Discussion
Month to Month Variance (based on ITD record data)
hi Anonymous
This is that you do not drag Year column into the visual, just like this:
Because that if you don't put year in the visual, it will calculate the max year data for "Date" table, in your case, it is 2025, so you just add a year column as above.
Regards,
Lin
I tried based on the MTD formula.
this is how my dax looks like
For current month :
MTDSales = CALCULATE(SUM(Sheet1[(RR)]),DATESMTD('Sheet1'[Period]))
For last month :
LastMTD = CALCULATE(SUM(Sheet1[(RR)]),DATESMTD(DATEADD(Sheet1[Period],-1,MONTH)))
Difference / variance :
CurrentRR = [MTDSales]-[LastMTD]
But doesnt look like its yielding the desirable result. i would guess the lastMTD is not calculatin correctly...
Community Supporthi Anonymous
The problem is that you need to use Date filed from dim date in the formula not 'Sheet1'[Period].
just adjust the measure as below:
MTDSales = CALCULATE(SUM(Sheet1[(RR)]),DATESMTD('Date'[Date]))LastMTD = CALCULATE(SUM(Sheet1[(RR)]),DATESMTD(DATEADD('Date'[Date],-1,MONTH)))CurrentRR = [MTDSales]-[LastMTD]Result:
here is a simple sample
by the way, when create measure or create visual, please use Date field from Dim date table, do not use date from fact table any more.
https://radacad.com/do-you-need-a-date-dimension
Regards,
Lin
Thanks Lin,
However ive tried that one before and it doesnt seems to work either.
i suspect it could because my dim date. this is how i set up my dim date tabel
Date = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))- v-lili6-msft
hi Anonymous
Please share your sample pbix file for us have a test, that will be a great help.
Regards,
Lin