Forum Discussion
Month on Month change
Negative Volume MoM% 2 =
IF(
ISFILTERED('categorysubcategoryVolume'[CombinedDate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_MONTH =
CALCULATE(
SUM('categorysubcategoryVolume'[Negative Volume]),
DATEADD('categorysubcategoryVolume'[CombinedDate].[Date], -1, MONTH)
)
RETURN
DIVIDE(
SUM('categorysubcategoryVolume'[Negative Volume]) - __PREV_MONTH,
__PREV_MONTH
)
)
I AM USING THIS DAX FOR MOM change , but my slicer has text values of dates which are in hierarchy format to make a default dynamic selection. so my mom change dax is not able to read the selection. how do i make this work ? i donot want to change my date filter in any way.. i tried creating a copy of the dates in the filter which are in the same table, but that does not seem to work either.
As a best practice and simplify time intelligence calculation, please use a separate dates table, you can create one in DAX or in M. Below is a sample DAX calc table.
CalendarTable = ADDCOLUMNS ( CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2030, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "MMMM" ), "Month Short Name", FORMAT ( [Date], "MMM" ), "Quarter", "Q" & QUARTER ( [Date] ), "Quarter Number", QUARTER ( [Date] ), "Week Number", WEEKNUM ( [Date] ), "Day", DAY ( [Date] ), "Day of Week", WEEKDAY ( [Date] ), "Day Name", FORMAT ( [Date], "dddd" ), "Day Short Name", FORMAT ( [Date], "ddd" ), "Is Weekday", IF ( WEEKDAY ( [Date], 2 ) <= 5, TRUE, FALSE ), "Year-Month", FORMAT ( [Date], "YYYY-MM" ), "Year-Quarter", FORMAT ( [Date], "YYYY" ) & "-Q" & QUARTER ( [Date] ), "Fiscal Year", IF ( MONTH ( [Date] ) >= 7, YEAR ( [Date] ) + 1, YEAR ( [Date] ) ), "Fiscal Quarter", "Q" & MOD ( QUARTER ( [Date] ) + 2, 4 ) + 1, "Day of Year", DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1 )Create a one-to-many single direction relationship from that to the fact table and mark it as date table
Disable Auto date/time
Create a measure similar to below:
Sales Previous Month = CALCULATE( [Sales], PREVIOUSMONTH ( 'Date'[Date] ) )Refer to the attached pbix for the details.
Otherwise, please post a workable sample data (not ang image) and your expected result from that.
Thanks for the reply from danextian.
Hi aditi1997yadav ,
Based on your description, I created sample data:
The fields used by the slicer are as follows:
1. Create a similar new column:
Column = DATE('Date_slicer'[Year], SWITCH('Date_slicer'[Month], "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12), 'Date_slicer'[Day])2. Create a new table:
3. Create the following relationship:
4. Result:
Best Regards,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- danextian
Super User
As a best practice and simplify time intelligence calculation, please use a separate dates table, you can create one in DAX or in M. Below is a sample DAX calc table.
CalendarTable = ADDCOLUMNS ( CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2030, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "MMMM" ), "Month Short Name", FORMAT ( [Date], "MMM" ), "Quarter", "Q" & QUARTER ( [Date] ), "Quarter Number", QUARTER ( [Date] ), "Week Number", WEEKNUM ( [Date] ), "Day", DAY ( [Date] ), "Day of Week", WEEKDAY ( [Date] ), "Day Name", FORMAT ( [Date], "dddd" ), "Day Short Name", FORMAT ( [Date], "ddd" ), "Is Weekday", IF ( WEEKDAY ( [Date], 2 ) <= 5, TRUE, FALSE ), "Year-Month", FORMAT ( [Date], "YYYY-MM" ), "Year-Quarter", FORMAT ( [Date], "YYYY" ) & "-Q" & QUARTER ( [Date] ), "Fiscal Year", IF ( MONTH ( [Date] ) >= 7, YEAR ( [Date] ) + 1, YEAR ( [Date] ) ), "Fiscal Quarter", "Q" & MOD ( QUARTER ( [Date] ) + 2, 4 ) + 1, "Day of Year", DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1 )Create a one-to-many single direction relationship from that to the fact table and mark it as date table
Disable Auto date/time
Create a measure similar to below:
Sales Previous Month = CALCULATE( [Sales], PREVIOUSMONTH ( 'Date'[Date] ) )Refer to the attached pbix for the details.
Otherwise, please post a workable sample data (not ang image) and your expected result from that.
Thanks for the reply from danextian.
Hi aditi1997yadav ,
Based on your description, I created sample data:
The fields used by the slicer are as follows:
1. Create a similar new column:
Column = DATE('Date_slicer'[Year], SWITCH('Date_slicer'[Month], "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12), 'Date_slicer'[Day])2. Create a new table:
3. Create the following relationship:
4. Result:
Best Regards,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.