Forum Discussion
Resolver IMonth-over-month not working as expected
Hi WillemC,
I'd like to suggest you add a calendar table and use calendar date to calculate data.
Steps:
1. Use date from original table to create a calendar table, then add the relationship.
Table = CALENDAR(FIRSTDATE(ALL('sample'[levdat])),LASTDATE(ALL('sample'[levdat])))2. Modify the formula and use the calendar date to calculate the data.
weight 1 month = IF( ISFILTERED('sample'[levdat]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."), VAR __PREV_MONTH = CALCULATE( SUM('sample'[weight]), DATEADD('Table'[Date].[Date], -1, MONTH) ) RETURN DIVIDE(SUM('sample'[weight]) - __PREV_MONTH, __PREV_MONTH) ) Weight MoM% 12 = IF( ISFILTERED('sample'[levdat]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."), VAR __PREV_MONTH = CALCULATE( SUM('sample'[weight]), DATEADD('Table'[Date].[Date], -12, MONTH) ) RETURN DIVIDE(SUM('sample'[weight]) - __PREV_MONTH, __PREV_MONTH) )3. Create matrix visual with above data.
Regards,
Xiaoxin Sheng
Resolver IHello Anonymous,
some basic data I have (it's very long, multiple values per month and day for each month since januari 2016 till april 2017):
year, month, weight, levdat
2016, 1, 40180, 4-1-2016 0:00:00
2016, 1, 39420, 7-1-2016 0:00:00
2016, 1, 39740, 9-1-2016 0:00:00
2016, 1, 40320, 15-1-2016 0:00:00
2016, 1, 40100, 16-1-2016 0:00:00
2016, 1, 40040, 28-1-2016 0:00:00
2016, 2, 37980, 2-2-2016 0:00:00
2016, 2, 38100, 3-2-2016 0:00:00
2016, 2, 40240, 6-2-2016 0:00:00
2016, 2, 39940, 6-2-2016 0:00:00
2016, 2, 40160, 18-2-2016 0:00:00
2016, 2, 40020, 20-2-2016 0:00:00
....
2017, 2, 39880, 4-2-2017 0:00:00
2017, 2, 39680, 14-2-2017 0:00:00
2017, 2, 40520, 15-2-2017 0:00:00
2017, 2, 40240, 16-2-2017 0:00:00
2017, 2, 38980, 17-2-2017 0:00:00
2017, 2, 38000, 22-2-2017 0:00:00
2017, 2, 37180, 24-2-2017 0:00:00
2017, 2, 40520, 24-2-2017 0:00:00
2017, 2, 39700, 25-2-2017 0:00:00
2017, 2, 40060, 25-2-2017 0:00:00
.....
The code i'm using
weight 1 month =
IF(
ISFILTERED('ds1'[levdat]);
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy.");
VAR __PREV_MONTH =
CALCULATE(
SUM('ds1'[weight]);
DATEADD('ds1'[levdat].[Date]; -1; MONTH)
)
RETURN
DIVIDE(SUM('ds1'[weight]) - __PREV_MONTH; __PREV_MONTH)
)
and
Weight MoM% 12 =
IF(
ISFILTERED('ds1'[levdat]);
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy.");
VAR __PREV_MONTH =
CALCULATE(
SUM('ds1'[weight]);
DATEADD('ds1'[levdat].[Date]; -12; MONTH)
)
RETURN
DIVIDE(SUM('ds1'[weight]) - __PREV_MONTH; __PREV_MONTH)
)
this results in a matrix that looks like this:
I've noticed that if the -1 or -12 counts the number of rows back from the max month (12).
So If I fill in -8, then only the 62.25% is shown and the rest is 0%.
If I fill in -9 then the values for month 4 and 5 are shown (just a small preview of when I cange the code vor Weight 1 month to -9)
What I want to accomplish is that month 5 shows the amount vs month 4
now it compares to the same month previous year when I insert -12. So I was assuming that -1 compares to the previous month.
Hi WillemC,
I'd like to suggest you add a calendar table and use calendar date to calculate data.
Steps:
1. Use date from original table to create a calendar table, then add the relationship.
Table = CALENDAR(FIRSTDATE(ALL('sample'[levdat])),LASTDATE(ALL('sample'[levdat])))
2. Modify the formula and use the calendar date to calculate the data.
weight 1 month =
IF(
ISFILTERED('sample'[levdat]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
VAR __PREV_MONTH =
CALCULATE(
SUM('sample'[weight]),
DATEADD('Table'[Date].[Date], -1, MONTH)
)
RETURN
DIVIDE(SUM('sample'[weight]) - __PREV_MONTH, __PREV_MONTH)
)
Weight MoM% 12 =
IF(
ISFILTERED('sample'[levdat]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
VAR __PREV_MONTH =
CALCULATE(
SUM('sample'[weight]),
DATEADD('Table'[Date].[Date], -12, MONTH)
)
RETURN
DIVIDE(SUM('sample'[weight]) - __PREV_MONTH, __PREV_MONTH)
)
3. Create matrix visual with above data.
Regards,
Xiaoxin Sheng
- WillemC
Thank you Anonymous
This solution indeed works.
I don't understand why this does work, and my first one does not.
Both dates are formatted the same way.
I even find this option less logical, since I don't understand how this part works now:
CALCULATE( SUM('sample'[weight]), DATEADD('Table'[Date].[Date], -1, MONTH) )How does Power BI knows what date to take here (for each period)?
It would be more logical to use the original 'sample'[Date].[Date]
Hi WillemC,
I think these "date calculate" function are based on date column(date calcualte functions means the function which will calculate through the all records , for e.g "DateAdd", "PreviousMonth"...). If your amount value are in the same table, these function seems not able to calculate through all records (the calculation are been limited).
Regards,
Xiaoxin Sheng
Follow-up to this. When using the quick measure it only asks for one date which I would assume would come from the calendar (date) table. The code displayed had to be editted to switch the data in the ISFILTERED to the date from the main table and to not use the Date table which I find confusing. I would think the quick measure would just work and not require code editting. Can some one explain this?
weight 1 month =
IF(
ISFILTERED('sample'[levdat]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
VAR __PREV_MONTH =
CALCULATE(
SUM('sample'[weight]),
DATEADD('Table'[Date].[Date], -1, MONTH)
)
RETURN
DIVIDE(SUM('sample'[weight]) - __PREV_MONTH, __PREV_MONTH)
)