Month over month change issue

[jayjani]

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): 

Index	Region	Location	Transfer Org	FY	Submission	Category	Q1	Q2	Q3	Q4
606	Geo	India	Compute Tower	FY19	Jun Flash	Cost	0.01	0.01	0.01	0.01
607	Geo	India	Compute Tower	FY19	Jul Flash	Cost	0.00	0.01	0.01	0.01
608	Geo	India	HIT	FY19	Jun Flash	Cost	0.07	0.09	0.14	0.14
609	Geo	India	HIT	FY19	Jul Flash	Cost	0.19	0.23	0.22	0.22
610	Geo	India	SDC	FY19	Jun Flash	Cost	0.01	0.01	0.01	0.01
611	Geo	India	SDC	FY19	Jul Flash	Cost	0.01	0.01	0.01	0.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:

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.

[jayjani]

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): 

Index	Region	Location	Transfer Org	FY	Submission	Category	Q1	Q2	Q3	Q4
606	Geo	India	Compute Tower	FY19	Jun Flash	Cost	0.01	0.01	0.01	0.01
607	Geo	India	Compute Tower	FY19	Jul Flash	Cost	0.00	0.01	0.01	0.01
608	Geo	India	HIT	FY19	Jun Flash	Cost	0.07	0.09	0.14	0.14
609	Geo	India	HIT	FY19	Jul Flash	Cost	0.19	0.23	0.22	0.22
610	Geo	India	SDC	FY19	Jun Flash	Cost	0.01	0.01	0.01	0.01
611	Geo	India	SDC	FY19	Jul Flash	Cost	0.01	0.01	0.01	0.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:

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.

[dax]

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

quantity MoM% = 

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

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

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 )
    )

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]

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.