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

Month over month change issue

Hello,

 

I need to calculate the Month over Month change in Flash. I have my Excel sheet formatted in the following way (without date stamp): 

 

IndexRegionLocationTransfer OrgFYSubmissionCategoryQ1Q2Q3Q4
606GeoIndiaCompute TowerFY19Jun FlashCost0.010.010.010.01
607GeoIndiaCompute TowerFY19Jul FlashCost0.000.010.010.01
608GeoIndiaHITFY19Jun FlashCost0.070.090.140.14
609GeoIndiaHITFY19Jul FlashCost0.190.230.220.22
610GeoIndiaSDCFY19Jun FlashCost0.010.010.010.01
611GeoIndiaSDCFY19Jul FlashCost0.010.010.010.01

 

First, I unpivoted my data in Power BI to have just one column for 'Quarters'. Then I split FY and Submission column to fetch 'Year' and 'Month'.

 

I created a separate 'Date Table' and adjusted Date as per my Org's Fiscal Year (FY starts in November).I linked my date table to source table by creating YYYYMM column in both. 

 

However, when I use the Quick Measure 'MoM change', it doesn't calculate the actual difference and results following:

Capture.PNG

I tried all filter combinations, so it is certainly not a filter issue. My date table is working perfectly - when I create a table with July filter on, the date shows (Fiscal Month - 9, Qtr - 3, and FY - 19), which it should as per my Fiscal schedule.

 

But the MoM% simply doesn't work. I also tried manual coding and it still doesn't pull previous month data.

 

If somebody could help, I would deeply appreciate it. I have been working on this the entire week.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Month over month change issue

Hi jayjani,

In your post, it seems that you want to use quick measure for MoM change, right? If so, I think you need a date column. You could change measure like below

102.png

 

quantity MoM% = 

    VAR __PREV_MONTH =
        CALCULATE(
            SUM('Table7'[quantity]),
            DATEADD('Table7'[date], -1, MONTH)
        )
    RETURN
        DIVIDE(SUM('Table7'[quantity]) - __PREV_MONTH, __PREV_MONTH)

103.png

If you don’t have date column, and your data sample is like below

104.png

You could add another index column for this table, and use measure like below

Measure 9 =
VAR PRE =
    CALCULATE (
        SUM ( MOM[Value] ),
        FILTER (
            ALL ( MOM ),
            MOM[Index] = MIN ( MOM[Index] )
                && MOM[Transfer Org] = MIN ( MOM[Transfer Org] )
                && MOM[ID]
                    = MIN ( MOM[ID] ) - 1
        )
    )
RETURN
    IF (
        PRE = 0
            || ISBLANK ( PRE ),
        0,
        DIVIDE ( ( SUM ( MOM[Value] ) - PRE ), PRE )
    )

105.png

Best Regards,

Zoe Zhi

 

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

jayjani Frequent Visitor
Frequent Visitor

Re: Month over month change issue

Sorry, but it  doesn't work. My data has Q1 to Q4 fields for every month. So, that ID algorithm will produce QoQ change, not MoM change. If you see the picture, I have Q1 to Q4 data for both June and July.

 

I tried adding date column in Power Query in Excel but it still doesn't produce the result.

 

I even tried manually adding date, but no success. I also changed my nomenclature from Q to Qtr in case it was messing up with BI's automatic Quarter fields. That didn't help as well.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 149 members 1,773 guests
Please welcome our newest community members: