Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I'm new to Power BI and testing and trying out some different things to get the hang of the programm.
At the moment I'm testing the Quick Measure Month-over-month change.
It generates some nice code for me, but it's not working as I expected.
The code that's generated:
M.o.m. = IF( ISFILTERED('ds1'[levdate]); 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'[levdate].[Date]; -1; MONTH) ) RETURN DIVIDE(SUM('ds1'[Weight]) - __PREV_MONTH; __PREV_MONTH)
I'm expecting that my sales for a month (i.e. april) is compared to the previous month march.
But when I create a simple matrix (using month as rows and m.o.m. as values), all values are 0%
When I change the -1 to -12 (a year), suddenly values appear in the table.
Why does it work if I look back one year, but not when I'm comparing one or some other amount of months?
I fiddled around with this, creating different tables, altering the code etc. But I can't get it to work to compare to the previous month.
Please enlighten me... ( I know I can create it with sql query on import, but I'm trying to learn the capabilities of Power BI)
Solved! Go to Solution.
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
Hi @WillemC,
Can you please share some sample data to test?
Regards,
Xiaoxin Sheng
Hello @v-shex-msft,
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
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)
)
Thank you @v-shex-msft
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |