Month on Month change

[aditi1997yadav]

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. 

[danextian · Accepted solution]

Hi @aditi1997yadav 

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.

[Anonymous · Accepted solution]

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,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @aditi1997yadav 

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.