cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lhb887 Frequent Visitor
Frequent Visitor

Sample Request -> WOW, MOM, YOY (using calendar table)

I'm learning and using PBI recently.

I learned how to make the 'calendar' table and link to the main table('Sales' that have a [date]).

I did make YOY %! - using quick measure,,

 

But when I do the same thing on MOM, the number appeared as 0.. and don' know how to make WOW

 

Can I request the sample of Quick measures (or DAX) to get WOW, MOM, YOY ??

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Sample Request -> WOW, MOM, YOY (using calendar table)


@lhb887 wrote:

I'm learning and using PBI recently.

I learned how to make the 'calendar' table and link to the main table('Sales' that have a [date]).

I did make YOY %! - using quick measure,,

 

But when I do the same thing on MOM, the number appeared as 0.. and don' know how to make WOW

 

Can I request the sample of Quick measures (or DAX) to get WOW, MOM, YOY ??

 

Thank you.


@lhb887

See a demo as attached.

 

value MoM% =
VAR __PREV_MONTH =
    CALCULATE ( SUM ( 'table'[value] ), DATEADD ( 'calendar'[Date], -1, MONTH ) )
RETURN
    DIVIDE ( SUM ( 'table'[value] ) - __PREV_MONTH, __PREV_MONTH )

value WoW% = 
VAR __PREV_WEEK =
    CALCULATE (
        SUM ( weekdataTable[Value] ),
        FILTER (
            ALLSELECTED ( 'weekdataTable' ),
            RELATED ( 'calendar'[week] )
                = MAX ( 'calendar'[week] ) - 1
        )
    )
RETURN
    IF(ISBLANK(SUM(weekdataTable[Value])),BLANK(),DIVIDE ( SUM ( 'weekdataTable'[value] ) - __PREV_WEEK, __PREV_WEEK ))

Capture.PNG

 

1 REPLY 1
Moderator Eric_Zhang
Moderator

Re: Sample Request -> WOW, MOM, YOY (using calendar table)


@lhb887 wrote:

I'm learning and using PBI recently.

I learned how to make the 'calendar' table and link to the main table('Sales' that have a [date]).

I did make YOY %! - using quick measure,,

 

But when I do the same thing on MOM, the number appeared as 0.. and don' know how to make WOW

 

Can I request the sample of Quick measures (or DAX) to get WOW, MOM, YOY ??

 

Thank you.


@lhb887

See a demo as attached.

 

value MoM% =
VAR __PREV_MONTH =
    CALCULATE ( SUM ( 'table'[value] ), DATEADD ( 'calendar'[Date], -1, MONTH ) )
RETURN
    DIVIDE ( SUM ( 'table'[value] ) - __PREV_MONTH, __PREV_MONTH )

value WoW% = 
VAR __PREV_WEEK =
    CALCULATE (
        SUM ( weekdataTable[Value] ),
        FILTER (
            ALLSELECTED ( 'weekdataTable' ),
            RELATED ( 'calendar'[week] )
                = MAX ( 'calendar'[week] ) - 1
        )
    )
RETURN
    IF(ISBLANK(SUM(weekdataTable[Value])),BLANK(),DIVIDE ( SUM ( 'weekdataTable'[value] ) - __PREV_WEEK, __PREV_WEEK ))

Capture.PNG

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 109 members 1,553 guests
Please welcome our newest community members: